[SQL2005] truncate table 40Go [Résolu]

[dkmix]

Bonjour,

je souhaite supprimer des données dans une table de 40Go.

Sans me douter que l'instruction "delete" inscrivait des choses dans tempdb, j'ai d'abord fait un "delete" (en ayant suffisamment de place sur mon disque de log).

çà n'a pas marcher (saleté de tempdb, j'avais prévu pour les logs mais pas pour mes bases système).

Bref je veux donc selectionner mes tuples dans une table temporaire, tronquer la table et remettre les tuples dans ma table, avec le script suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
--select into #tempSL
SELECT * INTO #tempSL FROM [NAV403REST_DEV].[dbo].[$Sales Line Archive]
WHERE [Document No_] IN (SELECT [No_]
	  FROM [NAV403REST_DEV].[dbo].[$Sales Header Archive]
	WHERE [Shipment Date] > '01/05/2011')
GO
 
 
--select into #tempSH
SELECT * INTO #tempSH
  FROM [NAV403REST_DEV].[dbo].[$Sales Header Archive]
WHERE [Shipment Date] > '01/05/2011'
GO
 
-- truncate des tables
 
TRUNCATE TABLE [NAV403REST_DEV].[dbo].[$Sales Header Archive]
GO
 
TRUNCATE * INTO [NAV403REST_DEV].[dbo].[$Sales Header Archive] FROM #temp
GO
 
-- select into tables d'origine
SELECT * INTO [NAV403REST_DEV].[dbo].[$Sales Header Archive]
FROM #tempSH
GO
 
SELECT * INTO [NAV403REST_DEV].[dbo].[$Sales Line Archive]
FROM #tempSL
GO
 
 
DROP TABLE #tempSH
GO
DROP TABLE #tempSL
GO

Mes questions sont :

Combien de temps prend un truncate sur une table de 40Go (à la louche) ?
Quel est l'impact sur la table pour les autres utilisateurs ?
Quel est l'impact sur la base de donnée pour les autres utilisateurs ?
Quel l'impacte sur l'instance pour les autres utilisateurs ?

Suis-je certain que le script ci dessus ne va rien écrire (hormis mes tables #temp qui ne feront pas plus d'une centaine de Mo) sur le disque qui héberge mes bases de données système (seulement 25Go de libre sur ce disque) ?

Pensez-vous que le script est viable ? sinon, comment puis-je l'optimiser ?

[mikedavem]

Bonjour,

Personnellement je ne travaillerais pas avec des tables temporaires mais plutôt avec des tables de travail classiques ce qui évitera dans un 1er temps d'aller occuper tempdb pour rien. De plus en cas d'erreur ou d'arrêt de votre instance les données seront sauvegardés. Dans une table temporaire ce n'est pas le cas.

Je verrais plus un script comme cela.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Récupération des lignes de vente à garder
SELECT * INTO tempSL 
FROM [NAV403REST_DEV].[dbo].[Restoria_test$Sales Line Archive]
WHERE [Document No_] IN (SELECT [No] 
                         FROM [NAV403REST_DEV].[dbo].[Restoria_test$Sales Header Archive]
	                     WHERE [Shipment Date] > '01/05/2011')
GO
 
-- Récupération des entêtes de vente à garder
SELECT * INTO tempSH
FROM [NAV403REST_DEV].[dbo].[Restoria_test$Sales Header Archive]
WHERE [Shipment Date] > '01/05/2011'
GO
 
-- truncate des tables header et lignes vente 
TRUNCATE TABLE [NAV403REST_DEV].[dbo].[Restoria_test$Sales Header Archive]
GO
 
TRUNCATE TABLE [NAV403REST_DEV].[dbo].[Restoria_test$Sales Line Archive] 
GO
 
-- Réinsertion des données à garder dans les tables entête et lignes de vente
INSERT INTO [NAV403REST_DEV].[dbo].[Restoria_test$Sales Header Archive]
SELECT * FROM tempSH
GO
 
SELECT * INTO [NAV403REST_DEV].[dbo].[Restoria_test$Sales Line Archive]
SELECT * FROM tempSL
GO
 
-- suppression des tables de travail
DROP TABLE tempSH
GO
DROP TABLE tempSL
GO

En principe vous n'aurez pas de problème avec TRUNCATE d'autant plus que sur Navision il est rare de voir des clés étrangères sur les tables. Même chose avec les colonnes IDENTITY .. celles-ci ne sont en principe pas utilisés.

Combien de temps prend un truncate sur une table de 40Go (à la louche) ?

Impossible à dire comme cela car beaucoup de paramètres entre en jeu mais il est certain que cette opération est beaucoup plus rapide que l'instruction DELETE puisque seules les désallocations de pages sont loggés.

Quel est l'impact sur la table pour les autres utilisateurs ?
Quel est l'impact sur la base de donnée pour les autres utilisateurs ?
Quel l'impacte sur l'instance pour les autres utilisateurs ?

Si vous faites cela , les utilisateurs ne pourront pas travailler pendant un moment sur vos tables. Cette opération est à faire de préférence dans un contexte hors production.

++

[dkmix]

Merci pour la réponse.

Script passé sur base de dev.
Pour info, le truncate a été très rapide (<2sec).

Cela ne libère pas la place sur le disque.
J'ai fait un DBCC SHRINKFILE('fichier.mdf') pour libérer de la place.

Contrairement à ce que je pensais , cette action a libérée 70GO !! (contre 40 attendu).
A priori, le plan de maintenance mis en place ne libère pas l'espace inutilisé (il y a surement une raison)

Je me demande quel est l impacte sur les performance (le fait de supprimer l'espace inutilisé) ?

Si impact il y a, existe-il une autre méthode pour gérer l'espace inutilisé ?

[elsuket]

Bonjour,

Cela ne libère pas la place sur le disque.

Effectivement cela ne fait que désallouer les pages et supprimer les pointeurs des index, ce qui fait qu'elles sont réutilisables pour les données "à venir".

Ce qui fait que TRUNCATE est plus rapide que DELETE, c'est que DELETE enregistre dans le fichier du journal des transactions de la base de données toutes les données affectées par le DELETE, de façon à pouvoir l'annuler.

En revanche une instruction TRUNCATE n'est pas annulable, donc c'est une instruction à manipuler avec parcimonie

A priori, le plan de maintenance mis en place ne libère pas l'espace inutilisé (il y a surement une raison)
Je me demande quel est l impacte sur les performance (le fait de supprimer l'espace inutilisé) ?

C'est tout à fait normal si le plan de maintenance ne contient pas de SHRINKFILE, qui doit être réservé seulement aux cas d'urgence, comme un manque d'espace disque.
Un fichier (de données ou du fichier du journal des transactions) grossit pour pouvoir y stocker des données : jusque-là, rien de spécial.

Maintenant chaque opération de grossissement de fichier étant particulièrement lente, le moteur de base de données ne rétrécit jamais de lui-même les fichiers, puisque de toute façon les fichiers sont voués à stocker de nouvelles données.
Donc quand un grossissement de fichier est en cours, toutes les transactions qui doivent écrire dans ce fichier "patientent" en attendant que le fichier ait fini de grossir ... jusqu'à la prochaine fois

De plus cela favorise la fragmentation du fichier, qui pénalise également les performances de votre base de données.

Si impact il y a, existe-il une autre méthode pour gérer l'espace inutilisé ?

Normalement à la création de la base de données, on taille les fichiers de celle-ci de façon à ce qu'ils n'aient pas ou peu à grossir.
Comme je l'ai écrit plus haut, un rétrécissement de fichier est une manœuvre d'urgence.
Au prix actuels des disques durs, cela ne doit plus être un problème

@++

[Waldar]

Envoyé par elsuket

En revanche une instruction TRUNCATE n'est pas annulable, donc c'est une instruction à manipuler avec parcimonie

C'est vrai chez Oracle mais j'ai toujours cru que SQL-Server pouvait rollbacker certaines instructions DDL.
Avec un test "quick and dirty" ça a l'air d'être le cas :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE toto ( col integer )
 
INSERT INTO toto (col) VALUES (1)
-- (1*ligne(s) affectée(s))
 
begin transaction
 
  SELECT count(*) FROM toto -- 1 -- (1*ligne(s) affectée(s))
 
  TRUNCATE TABLE toto
 
  SELECT count(*) FROM toto -- 0 -- (1*ligne(s) affectée(s))
 
rollback transaction
 
SELECT count(*) FROM toto -- 1 -- (1*ligne(s) affectée(s))

[mikedavem]

En revanche une instruction TRUNCATE n'est pas annulable, donc c'est une instruction à manipuler avec parcimonie

Pas tout à fait vrai. TRUNCATE est loggé dans le journal mais différement ... nous n'avons que les désallocations de pages et cela suffit de pouvoir revenir en arrière au cas où. A l'intérieur d'une transaction (BEGIN TRAN ... ROLLBACK TRAN) il est tout à fait possible de retrouver ses données en cas de ROLLBACK : cf exemple de Waldar.

++