Réduire considérablement la taille de vos bases de données SQL

Si vous avez fait le tour de vos bases de données, vous avez remarquer que pour chacune d'elle, SQL génère 2 fichiers, soit un fichier .MDF et un autre .LDF. Le premier étant relativement petit mais le second peut représenter plusieurs Gigaoctet. Alors comment réduire l'espace utiliser par le .LDF.

Commençons d'abord par répondre à la 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'

À 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. 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.