How-To: réduire la taille du journal de transactions sur disque

Deuxième how-to vidéo sur SQL Server, sur le sujet N°1 remonté dans les forums d’entraide SQL Server: comment réduire la taille d’un journal de transactions sur disque.

Pourquoi il explose:

Avant de vouloir le réduire, il faut comprendre pourquoi on en est arrivé là. A l’origine de ce problème, une option par défaut de la base de données qui s’appelle RECOVERY. Elle permet d’indiquer deux choses:

• Quels types de sauvegarde il sera possible d’effectuer sur la base.
• De quelle manière on recycle les transactions validées dans le journal de transactions.

Cette option a trois valeurs possibles:

• FULL:toutes les transactions attendent d’être sauvegardées pour être purgées du fichier LDF. Il n’y a qu’une sauvegarde de transactions qui puisse les purger. Full est le mode par défaut pour toute nouvelle base utilisateur.
• SIMPLE: les transactions validées sont purgées du journal à intervalles réguliers par un processus d’arrière plan, le CHECKPOINT. Cette option correspond à l’ancien mode ‘trunc log on checkpoint’ qui existait en 7.0.
• BULK_LOGGED: permet de minimiser la journalisation de certains ordres de modification massive de données, tels que les reconstructions d’indexes par exemple.

Pour plus de détails sur ces différents modes, voir les épisodes 1 et 2 sur les modes de récupération.

On peut lire cette option en utilisant la commande suivante:

select DATABASEPROPERTYEX('CAPDATA','RECOVERY')
GO

------------
FULL

FULL est la valeur utilisée par défaut lors de la création d’une base. Ce qui signifie que si on ne fait jamais de sauvegardes de transactions sur une base dans ce mode, les transactions ne seront jamais purgées du journal et au bout de 6 mois ou d’un an on arrive à saturer l’espace disque, d’où le besoin de réduire la taille de ce fichier. Il existe une technique pour le ramener à sa taille initiale en moins de 2 minutes, comme expliqué dans le paragraphe suivant et dans la démo vidéo ci-dessus. Pour savoir si un backup transactionnel a déjà été exécuté sur une base, on peut interroger la table backupset de la base msdb:

select backup_start_date, backup_finish_date, type, recovery_model
from msdb.dbo.backupset where database_name = 'CAPDATA' and type = 'L'

backup_start_date              backup_finish_date         type          recovery_model
--------------------------     -------------------------- ------------- -------------------
2010-09-07 18:54:42.000           2010-09-07 18:54:52.000            L           FULL

Comment le réduire:

En deux étapes:

• Etape 1: on vide le journal de son contenu, ce qui revient à recycler simplement les transactions à l’intérieur de l’enveloppe, sans la réduire.
• Etape 2: on réduit l’enveloppe sur disque: une fois le journal vidé, il peut être réduit avec la commande DBCC SHRINKFILE ().

Si vous n’avez jamais fait de sauvegarde de journal, il y a des chances pour que la taille du fichier LDF soit assez conséquente. S’il s’agit de plusieurs dizaines ou centaines de Gb, et que vous faites des sauvegardes complètes tous les jours par exemple, ça ne sert à rien de sauvegarder toutes ces transactions qui sont entérinées depuis belle lurette. Si on regarde le contenu du journal de transactions avant de faire la manoeuvre;

dbcc sqlperf(logspace)

Database Name       Log Size (Mb)       Log Space Used (%)      Status
------------------  ------------------  ---------------------   ---------
CAPDATA               25889,367                91,7766              0

La technique consiste à passer la base en mode SIMPLE:

ALTER DATABASE [CAPDATA] SET RECOVERY SIMPLE
GO

Un checkpoint est exécutée automatiquement dès la fin de la commande. Toutes les transactions validées sont purgées du journal . Techniquement elles ne sont pas effacées mais leur conteneur physique  est réinitialisé et peut être réutilisé:

dbcc sqlperf(logspace)

Database Name       Log Size (Mb)       Log Space Used (%)      Status
------------------  ------------------  ---------------------   ---------
CAPDATA               25889,367                1,192919              0

Il ne reste plus qu’à réduire le fichier journal de transactions avec un DBCC SHRINKFILE. A cette étape il faut décider quelle est la nouvelle taille à donner au journal de transactions. Si on le réduit à son maximum, il risque de grandir à nouveau pour atteindre sa taille de croisière, et si on souhaite de réagrandir ensuite, la portion allouée sera zéro-initialisée [1]. Idéalement il faudrait un historique d’utilisation de l’espace dans le journal pour savoir combien d’espace consomme la plus grosse transaction. Par exemple dans notre cas, 50Mb:

select name from CAPDATA.sys.database_files where Type_DESC='LOG'

name
---------------
GESTIMMO_log01

dbcc shrinkfile('GESTIMMO_log01',50)

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
5      2           6400        6400        6400        6400

6400 pages de 8192 octets = 50Mb, on a réussi à réduire l’enveloppe sur disque de 25Gb à 50Mb en quelques secondes.

Quoi faire ensuite:

Il faut décider de la stratégie de backup à adopter pour cette base: quelle quantité de données est-il acceptable de perdre: 1 heure, 4 heures, 1 journée, 1 semaine ?

• Si la réponse est inférieure à la fréquence des backups complets, alors il faut repasser la base en mode FULL, relancer un backup complet pour réinitialiser la chaîne de sauvegarde, puis planifier des backups de journaux via l’agent ou un plan de maintenance par exemple. Les backups de journaux permettent de garantir une restauration à tout point dans le temps en mode de récupération FULL.
• Si la réponse est au moins égale à la fréquence des backups complets, alors laisser la base en mode SIMPLE, et l’espace sera automatiquement recyclé.

A suivre un autre How-To sur les problèmes d’administration de base de SQL Server.

A+ David B.

Notes:

[1]: l’initialisation instantanée (IFI) ne s’applique qu’aux fichiers de données MDF et NDF, pas aux journaux de transactions.

Continuez votre lecture sur le blog :

• Modes de récupération et journal de transactions, épisode 1 (David Baffaleuf) [SQL ServerVintage]
• Modes de récupération et journal de transactions, épisode 2 (David Baffaleuf) [SQL ServerVintage]
• Principes d’une sauvegarde à chaud (David Baffaleuf) [SQL ServerVintage]
• How-To : réduire l’enveloppe de tempdb (David Baffaleuf) [SQL Server]
• Point-in-time recovery et fn_dump_dblog() (David Baffaleuf) [SQL Server]