Plan de maintenance : Mon log grossit !!! (trop ?)
Je vais faire un petit topo sur le plan de maintenance que tout DBA se doit de mettre en place sur ses bases de données. Enfin sur un point particulier du plan de maintenance. Aprés une sauvegarde complète, pourquoi la prochaine sauvegarde du journal de transaction est elle énoôôÔÔôorme…. ?
Avant d’aller plus loin, je me base sur les plans de maintenance, mais il y’a ceux qui préfèrent pour tout se cogner à la main, pour plus de contrôle sur leur plan de maintenance, pour ceux là, je vous conseille de voir par ici : All in one by Hallengren
Après y’a ceux qui passent par les plans de maintenance. Solution intégrée dans SQL SERVER 2005 et 2008, s’appuyant sur des lots SSIS.
Avant d’exposer le problème voici un exemple de plan de maintenance que j’ai rencontré :
Dans ce plan j’ai 3 sous plans :
- Le premier (copie d’écran précédente) effectue une sauvegarde complète de la base de données, après avoir fait un CheckDB, une reconstruction complète de l’index et une mise à jour des statistiques.
- Le deuxième sous plan effectue une sauvegarde différentielle, avec une réorganisation de l’index (bon ça c’est pas obligatoire, ça dépend de votre stratégie)
- Enfin le troisième sous plan effectue une sauvegarde du journal de Log
Au niveau du scheduling, partons sur une théorie qui en vaut une autre : Un backup complet la nuit, un différentiel à Midi et le journal de log toutes les heures.
(Note : Le Update Statistics dans la copie d’écran précédente ne SERT à RIEN : Voir le post de Dieu Paul Randal sur le sujet : http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx)
Voici les données du problème : APRES le travail du premier sous plan (sauvegarde complète) la sauvegarde de mon journal de log est énorme (mettons pour l’exemple 100 Mo), alors que ce travail journalier ne représente que peu de transactions et ne génère qu’une petite sauvegarde (mettons 1 Mo)
Voici une copie d’écran de ce qui se passe sur mon poste de dév :
Bon chez moi, avec le peu de données et surtout pas beaucoup de mouvements sur ma base de test, ça représente pas grand chose, mais imaginez ce qui arrive sur une base de données de plusieurs GO ou TB.. ça peut poser très vite un soucis, surtout si celle ci est beaucoup utilisée (ce qui est quand même le principe d’utilisation d’une base !)
Pour exemple, j’ai un client qui travaille sur Team Foundation Server et dont le log sauvegardé après le backup complet fait exactement la taille de la base de données, soit prés de 200 Go…. C’est long et ça prend de la place !
Mais surtout, avant de penser à “comment” résoudre ce problème, c’est “pourquoi” ça grossit comme ça lors de la sauvegarde du premier journal de transaction après la sauvegarde complète ?
Une Fausse idée : Un backup full NE génère PAS d’entrée des données sauvegardées dans le journal de Log (enfin si peu). Bon ça c’est dit, et on l’oublie pas.
Par contre, si, une ré indexation complète EST journalisée, toutes les opérations sont écrites dans le journal de log. Et quand un fait un Rebuild, bah il écrit toutes les données réindexées dans le journal de log (modulo les index non fragmentés non reconstruits) Idem pour la mise à jour des stats par exemple.
Du coup on s’étonne moins de retrouver un log qui correspond presque à la taille de notre base ! (imaginez sur une très très très grosse base) Tout dépend du niveau de fragmentation des index, bref, problématique.
Alors la solution ? En fait, Y’a 2 solutions, tout dépend de votre architecture.
1) Vous disposez de suffisamment de place pour stocker votre log énormissime.
STOP ! Laissez le comme ça le pauvre (enfin pauvre… façon de parler ;)).
NE FAITES PAS de shrink de votre log. SQL SERVER aura besoin de cet espace au prochain backup complet. Ne faites pas de shrink, vous ne ferez que générer de la fragmentation de votre disque dur, inutilement.
NE FAITES PAS DE SHRINK DANS VOS PLANS DE MAINTENANCE (Vous sentez le cri du coeur là ? Y’a pas UNE société où je ne rencontre pas cette hérésie dans les plans de maintenance ! Prenez des bonnes résolutions pour cette année 2010, virez moi ça de vos plans de maintenance)
Ok votre log ne génère que 10% d’espace en journée, c’est un fait. Mais cet espace est NECESSAIRE à SQL SERVER lors de son plan de maintenance. NE FAITES PAS DE SHRINK (on le dira jamais assez)
2) Vous ne disposez pas de suffisamment de place pour stocker un log énormissime.
Bon, là il va falloir faire des concessions. D’abord cette technique ne fonctionnera pas si vous avez votre base de données en mode Mirroir ou en réplication transactionnelle par exemple (dans ce cas, cf solution 1 et achetez du disque dur :))
Si ce n’est pas le cas, on peut créer une solution qui va désactiver le log pendant la reconstruction de l’index complet et le réactiver juste avant le backup (puisque celui ci ne génère pas grand chose dans le log)
SI vous voulez vous convaincre de cette solution, un petit tour sur ce post (de Dieu, encore une fois) devrez vous convaincre de continuer la lecture de ce post :
http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx
Bon, l’idée c’est de désactiver l’écriture du journal de log avant la ré indexation et de la réactiver juste après.
Note: je n’utilise pas l’instruction BACKUP LOG MaBase WITH TRUNCATE_ONLY, celle ci est devenue obsolète. Je pointe vers un sujet de Christian sur ce point là : http://blogs.codes-sources.com/christian/archive/2007/08/19/sql-server-interdire-les-op-rations-de-troncation-du-journal-de-transaction-truncate-only-no-log.aspx
Nous utilisons donc une astuce similaire qui consiste à passer du mode de recouvrement Complet au mode de recouvrement Simple (et re hop, re post de Christian sur le sujet : http://blogs.codes-sources.com/christian/archive/2007/02/12/sql-server-faq-sql-pourquoi-mon-fichier-de-log-ldf-est-il-aussi-gros-comment-diminuer-sa-taille.aspx )
Voila ce que ça donne lors de la modification de mon plan de maintenance (Notez au passage que j’ai fais sauter le Update Statistics, qui sert à rien ici)
Etape 1 : Passage en mode simple
Etape 2 : Re passage en mode complet (à placer après la réindexation)
Ce qui donne un plan de maintenance ressemblant à peu prés à ça :
Après exécution de ce plan là, et exécution de la sauvegarde du log, on retrouve quelque chose de plus … concis :)
Voilà une solution qui permet de sauvegarder de l’espace disque, sans passer par du Shrink File ABUSIF ! (on le dira jamais assez !!!)
Bon Plan de maintenances de Noel à tous !