Vider une très grosse table

**a4i.ludo** · 10/06/2013, 11h27

Bonjour à tous,

J'ai besoin de votre aide car je suis admin système et je bloque sur un petit soucis :

J'ai une DB SQL Server 2005 qui a pris de la taille avec la temps et je me retrouve avec une table qui est remplis de 35 Go de données.

Le support du produit qui utilise cette db ma dit que je pouvais la vider en supprimant le contenus des tables (via l'interface) et de ne pas prendre en compte les avertissements sur les dépendances.

Mais j'ai deux soucis :
- Lister le contenus de ces tables prend un temps énorme (des heures car des millions de lignes)
- Rien qu'en essayant de supprimer une ligne j'obtiens ce message :

Pouvez-vous m'aider à vider ces tables ?

Merci

**darkelend** · 10/06/2013, 11h47

Pour vider entièrement la table s'il n'y a pas de contrainte, il suffit de faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TRUNCATE TABLE NomDeLaTable

Ca va supprimer toutes les lignes. lien msdn

**elsuket** · 10/06/2013, 12h14

Bonjour,

Effectivement la solution du TRUNCATE TABLE est la plus efficace si la table n'est pas référencée.

Cela étant, le libellé de l'erreur, String or binary data would be truncated, suggère que les lignes sont archivées dans une autre table dont une ou plusieurs colonnes n'a pas la même taille que la même colonne dans la table que vous souhaitez nettoyer.

Ensuite s'il est long de retrouver des lignes dans cette table, cela n'a rien à voir avec le fait qu'elle contient des millions de lignes, mais probablement à la qualité de la requête, des statistiques de colonne de la table, et de ses index.

Par exemple, dans l'entreprise où je travaille, une des bases de données pèse 1.6 To, et l'un de ses tables contient un peu plus d'un milliard de lignes, et n'est pas partitionnée ... et on n'a pas de problèmes avec

.
Je ne crois pas qu'une table avec 1 milliard de lignes soit un record

@++

**a4i.ludo** · 10/06/2013, 14h23

Tout d'abord merci pour vos réponses rapide et claires

Je suis en train de faire un backup de mon serveur avant de manipuler car je ne suis pas toujours à l'aise avec les DB, je testerai ça en fin de journée.

Concernant le temps qu'il met à lister les lignes j'ai remarqué que lorsque j'ouvrais le contenu d'une de ces tables, toute la RAM finissait par être utilisée et ensuite il passait au paging file de Windows et remplissait le disque système par la même occasion

Une fois SQL management fermé, tout revient à la normale.

Je test ça dès que possible et je reviens poster ici

**aieeeuuuuu** · 10/06/2013, 14h33

Bonjour,

C'est surtout que SSMS n'est pas vraiment fait pour ouvrir/modifier des tables qui font des millions de lignes. Cet outil peut être pratique en développement pour modifier rapidement une table de quelques dizaines de lignes à la main.

Le plus long n'est pas pour que le moteur SQL récupérer les lignes, mais bien pour l'IHM de SSMS les afficher dans un joli tableau... A mon avis, c'est cette mise en page qui provoque le comportement que vous indiquez

**a4i.ludo** · 10/06/2013, 18h45

J'ai exécuté la commande TRUNCATE TABLE et j'obtiens le message suivant :

Problème de dépendance ?

Merci

**darkelend** · 11/06/2013, 09h26

Ca veut dire que tu as une clef étrangère. Donc en théorie, il faudrait vider d'abord la table référencée avant de pouvoir vider ta grosse table. Et là si tu ne sais pas si tu peux vider la seconde table, tu ne peux pas faire grand chose sans briser l'intégrité de tes données...

**a4i.ludo** · 11/06/2013, 09h33

En gros cette DB est composée de trois tables et les trois peuvent être vidées.
J'obtiens ce message sur la première et la seconde table, la troisième s'est bien vidée !

Donc si je comprend bien il y a un lien entre la table 1 et la 2 ?
Comment casser ce lien ?

**aieeeuuuuu** · 11/06/2013, 10h03

après avoir vidé la troisième table, avez vous réessayé les deux autres ?

si vous avez une table client, et une table facture qui référence client, vous ne pouvez pas vider client tant que des factures référencent des clients, par contre après avoir vidé facture, vous pouvez vider client...

**a4i.ludo** · 11/06/2013, 10h14

Oui j'ai tenté de vider les deux premières mais même résultat que précédemment.
J'en déduis que la troisième table était indépendante.

Comment faire pour les deux premières ?

**aieeeuuuuu** · 11/06/2013, 11h13

Quelle commande exécutez vous ?

Faites des DELETE et non des TRUNCATE s'il existe des contraintes d'intégrité, même si les tables référençant sont vides, TRUNCATE n'est pas applicable.

**a4i.ludo** · 11/06/2013, 11h35

J'utilisais "TRUNCATE TABLE nom_de_la_table".
Je viens de faire un 'DELETE nom_de_la_table' sur une table de 35 Go et ça mouline ! Je vois la taille de la table descendre mais cela va visiblement tourner pendant au moins une heure...

Je repost dès qu'il a terminé, merci pour le coup de main

**darkelend** · 11/06/2013, 11h37

effectivement ca va durer assez longtemps. Surveille la taille de ton fichier de log qui risque de grossir. Il faudra peut être le shrinker à la fin

**a4i.ludo** · 11/06/2013, 11h42

Envoyé par darkelend

effectivement ca va durer assez longtemps. Surveille la taille de ton fichier de log qui risque de grossir. Il faudra peut être le shrinker à la fin

Ça explique pourquoi, malgré la suppression du contenu des tables, l'espace disque diminue lentement mais surement.

Par contre je ne sais pas trop comment localiser la log en question, comment faire ?

J'ai regardé dans le dossier "LOG" mais rien qui ne dépasse les 20 Mo.
Je suis censé trouver un gros fichier ?

**aieeeuuuuu** · 11/06/2013, 11h45

clic droit sur la base>propriétés>fichiers

voir le fichier de type journal.

heu... rassurez moi... vous n’êtes pas en train de faire tout ça sur une base de production ?

**a4i.ludo** · 11/06/2013, 11h56

Envoyé par aieeeuuuuu

clic droit sur la base>propriétés>fichiers

voir le fichier de type journal.

heu... rassurez moi... vous n’êtes pas en train de faire tout ça sur une base de production ?

Non non j'ai cloné le serveur (virtuel sous vmware) et je bosse en offline sur le clone de la prod

PS : j'ai trouvé la log, je m'en occuperai plus tard

**a4i.ludo** · 11/06/2013, 14h08

Alors c'est bon les trois tables sont vidées !
J'ai un jolie fichier de log de 16Go sur lequel l'autogrowth est activé, je souhaiterai diminuer la taille de la log à 3Go, comment faire ?

**darkelend** · 11/06/2013, 14h20

Si ta base est en recovery model FULL, il faut sauvegarder ta log. http://msdn.microsoft.com/fr-fr/library/ms179478.aspx

Ensuite, il suffit de faire un shrink du log http://msdn.microsoft.com/fr-fr/libr...ql.105%29.aspx

**a4i.ludo** · 11/06/2013, 14h46

Envoyé par darkelend

Si ta base est en recovery model FULL, il faut sauvegarder ta log. http://msdn.microsoft.com/fr-fr/library/ms179478.aspx

Ensuite, il suffit de faire un shrink du log http://msdn.microsoft.com/fr-fr/libr...ql.105%29.aspx

C'est fait, sur ma lancée j'ai fait un shrink sur la base tant qu'a faire !
Apparemment tout va bien (en offline), je testerai en prod ce soir.
J'ai gagné beaucoup de place avec tout ça !

Merci à tous pour votre aide

Je passerai le sujet en résolu dès que ce sera testé en prod.

**elsuket** · 11/06/2013, 17h32

C'est fait, sur ma lancée j'ai fait un shrink sur la base tant qu'a faire !

Je ne sais pas à combien vous avez réduit le fichier de données, mais il ne faut pas "trop" le réduire; autrement la prochaine opération d'écriture devra attendre que le fichier ait d'abord grossi pour ce faire.

C'est pourquoi il est d'une part important de tailler les fichiers, qu'ils soient de données ou du journal des transactions, à la taille qu'on espère qu'ils auront après quelques années d'exploitation. Cela évite les grossissements de fichiers intempestifs, et la fragmentation physique du fichier.

D'autre part il est important d'activer la fonctionnalité d'initialisation instantanée de fichiers de données : cela se fait en donnant le privilège Perform Volume Maintenance Task dans la stratégie de sécurité locale, au compte de service qui exécute le service SQL Server. Cela permet à SQL Server de grossir un fichier sans avoir à mettre le nouveau morceau à zéro, et donc d'obtenir un gain significatif de temps également lors de la restauration d'une sauvegarde, même si cela ne s'applique pas au fichier du journal des transactions.
Enfin il est important de bien choisir la taille de l'incrément de chacun des fichiers : cela permet de réduire le nombre de grossissements et donc la possible fragmentation du fichier. Par ailleurs, lorsque le fichier du journal des transactions grossit énormément à remplir le volume qui l'héberge, cela évite de remplir complètement le disque, et en fin de compte de ne pouvoir effectuer aucune opération de purge puisqu'alors aucune transaction ne peut être écrite. Ne laissez donc pas 1Mo comme incrément de grossissement

En règle générale, on ne rétrécit jamais les fichiers de données, autrement que lorsqu'on a une contrainte d'espace disque, ou que l'on réalise une opération "grand nettoyage" comme vous ... mais il ne faut pas rétrécir au maximum

Enfin pour vous aider, j'ai publié une petite procédure stockée qui simplifie le monitoring du fichier du journal des transactions

@++