Discussion :

[Herlece]

Bonjour à tous, cela fait longtemps que je n'avais pas posté mais la je rencontre un gros souci avec une base de données.

En effet sur cette base le fichier de données fait 21go et le fichier log est passé de 30go à 120go en 2 semaines. J'ai fait un trunk une première fois et je suppose que je pourrai le refaire mais ce que je souhaite c'est connaître la raison de cette accroissemnt qui semble être incontrôlable. Avez-vous des techniques qui pourraient me permettre de voir ce qui augmente mon fichier log à ce point alors qu'au niveau des datas il ne bouge pas tant que ça.

Il y a des delete et des insert mais il s'agit de quelques millers de lignes par jour (en moyenne 8000 insert et 8000 delete) je ne crois pas que cela soit suffisant pour que le fichier log prenne 100go en 2 semaines.

Merci pour votre aide.

[Invité]

Est-ce que vous sauvegardez régulièrement le journal de transaction?

[Herlece]

Non une fois par mois.

[Invité]

lisez ceci : http://sqlpro.developpez.com/cours/sqlserver/log/

[Herlece]

Merci c'est gentil mais je l'ai déjà lu au moins 4 fois cette article. c'est de la que m'est venu l'idée de tronquer mon fichier log.

Ce que je voudrai c'est comprendre ce qui le fait grossir autant, est-ce normal si oui ok sinon quel remède apporté.

[Invité]

Ce que je voudrai c'est comprendre ce qui le fait grossir autant

Relisons donc une cinquième fois l'article ensemble ( http://sqlpro.developpez.com/cours/sqlserver/log/ )

Les fichier de log, sont en fait ce que l'on appelle en français les "fichiers de journalisation". Ils contiennent toutes les transactions effectuées dans la base de données (INSERT, UPDATE, DELETE..) enregistrée en séquence. Ce sont des fichiers en croissance forte qui peuvent parfaitement dépasser de beaucoup la taille des données. Par exemple si, partant d'une base vide, on ajoute et supprime un millier de lignes dans une table, et cela plusieurs centaines de fois, la base sera quasi vide et le journal des transactions important.
Il se peut donc que le journal des transactions vienne donc à occuper un espace disque important et si l'administrateur n'y prend pas garde, saturer les disques...

est-ce normal si oui ok sinon quel remède apporté.

normal : oui.
Remède: une sauvegarde régulière du journal de transaction. Tous les jours est une bonne base mais ça dépend de l'activité de votre base.

[Herlece]

oui encore merci. Mais je pensai qu'il pouvait y avoir autre chose. Ce n'est pas une critique de l'article, car j'apprécie énormément SQLPro, je dévore tous ces articles et j'y trouve tout le temps mes réponses.

Sauf que la je pensai qu'il pourrait y avoir une autre raison.

Je vais voir pour effectuer des sauvegardes quotidiennes juste avant les opérations d'insert et delete des données.

Maintenant une autre question lorsque la sauvegarde de la journée est faite je n'ai plus besoin de garder les log de la journée sauvegardée ?

Comment je pourrai supprimer ces log de la prod ?

Si je dis une annerie ne me tapez pas dessus. Je viens du monde de la BI.

[mikedavem]

Bonjour,

Le fait que votre journal se remplisse est tout à fait normal sur votre base de données surtout si vous effectuez de grosses opérations de mise à jour (INSERT, DELETE, UPDATE massifs). Ce genre d'opération met à jour les données de vos tables + les index qui pourraient exister ...

Je vais voir pour effectuer des sauvegardes quotidiennes juste avant les opérations d'insert et delete des données.

Dans votre cas, planifiez de façon plus judicieuse la sauvegarde des journaux (une fois par jour par exemple) pour éviter que le journal se remplisse et provoque un accroissement de votre fichier.

Maintenant une autre question lorsque la sauvegarde de la journée est faite je n'ai plus besoin de garder les log de la journée sauvegardée ?

Comment je pourrai supprimer ces log de la prod ?

Tout dépend de la stratégie de restauration de vos données et de la criticité de vos données ... Si vous effectuez une sauvegarde FULL de vos données à 20H tous les soirs et que vous pouvvez vous permettre de perdre une journée de travail .. la sauvegarde du soir peut suffir .. Dans ce cas vous pouvez même passer en mode de récupération simple pour ne pas vous préoccuper de la sauvegarde du journal de votre base de données.

Par contre, si vous devez restaurer dans le temps avec un minimum de perte de données (Données critiques) la sauvegarde du journal s'impose et vous devez absolument les garder pour pouvoir restaurer au plus près d'un crash par exemple.

++

[castorcharly]

Bonjour,

en fait je n'apporte pas une réponse de plus, mais une question, après avoir lu le petit papier de SQLpro.

J'utilise un SQL 2008, qui dans la console de management, sur un clic droit sur le nom de ma base, me propose "Réduire...".
Ce que j'ai fait, après avoir fait un backup/restaure de ma base.
En passant, elle fait 9 Go sur le mdf et 11 Go sur le log.

J'ai donc regardé les options de cette page Réduire et choisi Journal.
Après un laps de temps de 50 ms () mon journal est passé de 11 Go à 1 Mo...

Pouvez-vous me confirmer que je n'ai pas fait une bétise ?

Ceci est bien sur ma base de dev, ma base de prod étant dans une situation bien plus critique, log à 80 Go pour un mdf à 9 Go, j'attends vos commentaires éclairés pour faire la même manip dessus.

Ensuite, si cette action ne pose pas de problème ulterieur, est il possible de la lancer via une requête SQL ?

Merci de votre attention.

[mikedavem]

Les opérations de réduction du journal doivent être utilisé de façon exceptionnelle dans des situations d'urgence.

Il est préférable de planifier des sauvegardes plus rapprochés de votre journal selon le volume transactionnel joué sur votre serveur.

Le fait de réduire votre journal de façon récurrente comme cela va engendrer de la fragmentation logique au niveau de vos index et de la fragmentation physique sur votre système de fichiers ... bref la performance de votre base sera directement affectée.

Une autre solution pouvant être envisagée mais qui n'est pas forcément la meilleure est de passer en mode SIMPLE (si la stratégie de restauration vous le permet) et de vous affranchir de la sauvegarde de vos journaux. En mode SIMPLE, le journal est automatiquement tronqué.

++

++

[castorcharly]

Bonjour Mikedavem,

je suis étonné par votre réponse, car je pensais que le fichier LOG était juste de la journalisation pour récupérer les données en cas de problème.

Je ne pensais pas que ce fichier est un rôle sur les performances ou même sur les fonctionnalités de MSSQL !

Là je tombe de haut ! Quand je disais "bétise", je ne pensais vraiment pas à ça.

Merci quand même pour cette mauvaise nouvelle

[mikedavem]

je suis étonné par votre réponse, car je pensais que le fichier LOG était juste de la journalisation pour récupérer les données en cas de problème.

Oui le journal joue entre autre ce rôle (Le C de consistence des propriétés ACID d'une transaction).

Je ne pensais pas que ce fichier est un rôle sur les performances ou même sur les fonctionnalités de MSSQL !

Sur les performances si car c'est le fichier qui est directement impacté lors d'une transaction. Les opérations du journal se font d'abord en synchrone et les données sont ensuite écrites de manière asynchrone sur disque.

Fonctionnalité n'est peut être pas le terme à employer ici. Le mode de récupération FULL / BULK LOGGED ou SIMPLE dicte la manière dont on va utiliser le journal. Dans tous les cas il sera utilisé.

++

[SQLpro]

Bonjour Mikedavem,

je suis étonné par votre réponse, car je pensais que le fichier LOG était juste de la journalisation pour récupérer les données en cas de problème.

Je ne pensais pas que ce fichier est un rôle sur les performances ou même sur les fonctionnalités de MSSQL !

Là je tombe de haut ! Quand je disais "bétise", je ne pensais vraiment pas à ça.

Merci quand même pour cette mauvaise nouvelle

Lisez le blogs que j'ai écrit sur ce sujet :
http://blog.developpez.com/sqlpro/p5...fichiers-et-t/

A +

[castorcharly]

Mikedavem,

Merci encore pour ce complément d'info.

SQLpro,

A la lecture du lien : Je le jure, je ne me prends pas pour un DBA

J'ai bien ri, car le client voulait que la base de donnée soit stockée sur un NAS... Je ne savais pas qu'il était possible de forcer le fonctionnement sur un disque non local, mais de toute façon je ne l'aurais pas fait. A mon sens c'est trop dangerous.

Il n'empêche que c'est un sacré problème ce volume de log. Notre base est prévue pour un volume de données sur trois ans justement, (en fait deux, plus une année pour qu'ils réalisent les purges, je suis prévoyant).
Hors, mon ignorance de MSSQL fait que je ne savais pas que les imports massifs de données, provoquaient un tel phénomène avec le fichier de LOG.
Il y a un vrai DBA sur site, certifié MSSQL, qui s'occupera de gérer les volumes, mais moi sur mon poste de dev, je me tape des perfs, tant que ça reste supportable. Donc je ferai mes petits "réduire" sur ma machine et je laisse le DBA s'occuper de la machine de prod.

A chacun son métier, on est bien d'accord. Mais j'en sais un peu plus maintenant et ce n'est pas inutile.


Merci à tous les deux.

[dbaffaleuf]

Le fait de réduire votre journal de façon récurrente comme cela va engendrer de la fragmentation logique au niveau de vos index et de la fragmentation physique sur votre système de fichiers

Hello,

Je ne vois pas trop pourquoi réduire la taille du journal avec un dbcc shrinkfile toucherait à la fragmentation logique des indexes. Le faire sur un fichier de données d'accord, mais sur le journal ? Le shrink + truncateonly se contente de libérer l'espace jusqu'au dernier bloc alloué, non ?

Pour la fragmentation physique, on peut lancer le shrinkfile en précisant une taille pour ne pas provoquer une réallocation à la première transaction qui arrive ensuite. Le problème de la réallocation d'espace est qu'elle est toujours synchrone (elle bloque la CPU qui l'a initiée) et en plus l'espace alloué sera toujours zéro-initialisé dans le cas du journal.

[mikedavem]

Hello,

Je ne vois pas trop pourquoi réduire la taille du journal avec un dbcc shrinkfile toucherait à la fragmentation logique des indexes. Le faire sur un fichier de données d'accord, mais sur le journal ? Le shrink + truncateonly se contente de libérer l'espace jusqu'au dernier bloc alloué, non ?

Yes, tu as raison, je me suis attardé sur les fichiers de data. Sur le fichier journal ce sont les VLF qui sont simplement supprimés. Merci

Pour la fragmentation physique, on peut lancer le shrinkfile en précisant une taille pour ne pas provoquer une réallocation à la première transaction qui arrive ensuite. Le problème de la réallocation d'espace est qu'elle est toujours synchrone (elle bloque la CPU qui l'a initiée) et en plus l'espace alloué sera toujours zéro-initialisé dans le cas du journal.

Pour les fichiers journaux il me semble que leur fragmentation externe (donc physique) c'est ce va et vient continu entre une réduction et un autogrowth (bien souvent mal paramétré) des fichiers ... qui ne garantit pas que les écritures soient contigües sur disque. En plus il suffit que le paramètre autogrowth soit mal configuré pour avoir de la fragmentation interne (Trop de VLF).

++

[dbaffaleuf]

Pour les fichiers journaux il me semble que leur fragmentation externe (donc physique) c'est ce va et vient continu entre une réduction et un autogrowth (bien souvent mal paramétré) des fichiers ... qui ne garantit pas que les écritures soient contigües sur disque. En plus il suffit que le paramètre autogrowth soit mal configuré pour avoir de la fragmentation interne (Trop de VLF).++

C'est pour ça que je ne suis pas trop adepte du shrink. La volumétrie fait le yo-yo en permanence.

[SQLpro]

dans mon blog je donne comme image :
"Imaginons que vous construisez un parking. Il est plein, mais il y a encore de la surface non structurée. Une voiture arrive. Vous vous mettez à lisser le béton, peindre le sol et délimiter la place. La voiture repart, vous prenez votre marteau piqueur afin de détruire votre travail... Au moins avec une telle stratégie, c'est le secteur du bâtiment qui va y gagner !!!"

A +

[Herlece]

Bon encore une fois la communauté a agit. Merci à tous pour votre participation.
Je pense que je ne suis pas le seul à repartir avec les idées beaucoup plus claire.
Je clos ce post.

Voous êtes tous super