SQL Server vu par Christian Robert

SQL Server - FAQ SQL : Pourquoi mon fichier de log/ldf est il aussi gros ? Comment diminuer sa taille ?

Commençons pas répondre à la question par une autre question, qu'est ce que ce log ou ce fichiers LDF ?

C'est ce qu'on appelle le journal de transaction, il peut y avoir un ou plusieurs fichiers de journaux (appelés log en anglais) et ils portent généralement l'extension LDF.

Le journal de transaction contient l'ensemble des transactions exécutées sur la base de données. Pour simplifier imaginez que toutes les requêtes réalisant des écritures sont stockées dans ce journal avant même que les données soient écrites sur le disque. Il est donc normal que ce fichier grossisse au fil de l'activité de la base de données. Il n'est pas anormal que ce fichier grossisse mais il faut qu'il reste si possible à une taille fixe (en général on prend comme valeur de départ 20% de la taille des données), cette taille est à définir à la création de la base de données.

L'intérêt du journal de transaction, c'est qu'il vous sert à redémarrer votre base de données dans un état correct en cas d'arrêt brutal du serveur par exemple. Il sert aussi à annuler une modification si celle-ci implique un gros volume de données. Enfin il vous permet de restaurer une base de données très précisément dans le temps. Bref sans journal vous perdriez des données ou les performances de votre serveur seraient détestables (car les données ne sont jamais écrites immédiatement sur le disque, elles sont conservées en mémoire, par contre les transactions elles sont immédiatement inscrites sur le disque).

Comment diminuer la taille du fichier LDF ? Le seul moyen est de vider le journal et le seul moyen de vider le journal est de sauvegarder le journal de transaction :

-- Sauvegarde du journal de transaction de la base de données courante
-- Par convention on donne l'extention TRN à ce type de sauvegarde

BACKUP LOG MaBaseDeDonnees
TO DISK = 'c:\monrepertoire\monfichier.trn'

A ce moment là, le fichier du journal est vidé, mais il a toujours la même taille, pour forcer la diminution de la taille du journal de base de données il faut forcer la réduction de la taille de celui-ci, ce qui se fait grâce à :

-- Liste les fichiers et leurs nom pour la base de données courante
-- Vous renvoie le nom logique dont on a besoin plus loin
EXEC sp_helpfile

-- Indique l'espace libre dans le journal de transaction
DBCC SQLPERF(LOGSPACE)

-- DBCC SHRINKFILE( NomLogiqueDuFichier, TailleCibleEnMo, Option )
-- Diminue la taille du fichier spécifié, ici à 10 Mo au mieux
DBCC SHRINKFILE(mabase_log, 10)

-- Indique l'espace libre dans le journal de transaction
DBCC SQLPERF(LOGSPACE)

Sachez que la commande n'aura pas toujours d'effet, à cause entre autres :

• La partie active du journal se trouve actuellement à la fin du fichier de log
• Une sauvegarde est actuellement en cours
• Une transaction longue est en cours d'exécution
• Une réplication transactionnelle existe avec comme publication des données de cette base de données

De plus, jamais la totalité du journal n'est vidé, et donc la taille du fichier n'atteindra jamais 0 octets après un DBCC SHRINKFILE. Attention aussi à ne pas trop diminuer la taille du fichier, vous risquez quand celui-ci va à nouveau grossir de le fragmenter (en interne et sur le disque).

Autre solution, passez votre base de données en mode de récupération simple (il existe 3 modes de récupération : simple, journalisé en bloc, complet), dans ce mode vous n'aurez pas besoin de réaliser un BACKUP LOG pour vider le journal, cela se fait automatiquement à chaque point de contrôle (CHECKPOINT)… Le DBCC SHRINKFILE est toujours à exécuter pour réduire la taille du fichier.

-- Modifie le mode de récupération de la base de données
-- Dans ce mode les BACKUP LOG ne peuvent se faire
-- Le journal est tronqué automatiquement mais peut quand même grossir
ALTER DATABASE MaBaseDeDonnees SET RECOVERY SIMPLE

Attention dans ce mode, il est impossible de profiter du journal pour récupérer votre base de données, avec un RESTORE LOG, vous ne pourrez profiter que de la dernière sauvegarde complète de votre base de données.

Dernier point le BACKUP LOG avec NO_LOG ou TRUNCATE_ONLY, ceux-ci ne seront plus supportés dans les prochaines versions de SQL Server. En fait ces 2 commandes sont équivalentes à :

-- Remplace NO_LOG et TRUNCATE_ONLY
-- Passe en mode simple --> réalise un TRUNCATE jusqu'au point de contrôle
ALTER DATABASE MaBaseDeDonnees SET RECOVERY SIMPLE

-- Repasse en mode complet
ALTER DATABASE MaBaseDeDonnees SET RECOVERY FULL

-- Impérativement faire une sauvegarde complète après
BACKUP DATABASE MaBaseDeDonnees TO DISK = 'monFichier.BAK'

On supprime le contenu du journal de transaction jusqu'au dernier point de contrôle (CHECKPOINT). La sauvegarde complète est importante car sans elle, il serait impossible de faire une sauvegarde du journal après être revenu au mode de récupération complet.

Bonne lecture…