Comprendre et gérer le journal de transaction SQL Server

Toutes les informations et les codes temporels font référence à la vidéo : Comprendre les problèmes du journal de transaction dans SQL Server.

Le journal de transaction (LDF) est un composant essentiel de SQL Server. Il enregistre chaque modification effectuée sur la base de données pour garantir l’intégrité des données et permettre la récupération en cas de crash [00:03:18]. Sans une configuration correcte, ce fichier peut croître jusqu’à saturer votre stockage.

Diagnostiquer l’utilisation du disque

Pour comprendre l’état actuel de vos fichiers, SQL Server propose des rapports intégrés très visuels.

En faisant un clic droit sur votre base de données > Rapports > Rapports standards > Utilisation du disque, vous obtenez deux graphiques en camembert [00:01:04] :

• À gauche : L’utilisation du fichier de données (MDF).
• À droite : L’utilisation du journal de transaction (LDF).

Si votre journal est rempli à plus de 90%, il est temps d’intervenir.

Utiliser les requêtes système

Pour une vue d’ensemble de toutes les bases de l’instance, la commande classique reste :

DBCC SQLPERF(LOGSPACE);

Cette commande affiche la taille de chaque journal et son pourcentage d’utilisation [00:05:13]. Pour aller plus loin, vous pouvez utiliser des scripts plus complets.

USE master;
GO

-----------------------------------------------------------------
-- sp_logspace, replaces DBCC SQLPERF (LOGSPACE) with more
-- information
--
-- rudi@babaluga.com, go ahead license
-----------------------------------------------------------------
CREATE OR ALTER PROCEDURE sp_logspace
    @database SYSNAME = N'%'
AS BEGIN
    SET NOCOUNT ON;

	;WITH cte_vlf AS (
		SELECT 
			ROW_NUMBER() OVER(PARTITION BY d.database_id ORDER BY li.vlf_begin_offset) AS vlfid,
			CAST(PERCENT_RANK() OVER(PARTITION BY d.database_id ORDER BY li.vlf_begin_offset) * 100 as DECIMAL(5,2)) AS pr,
			d.name AS db, 
			li.vlf_sequence_number, 
			li.vlf_active, 
			li.vlf_begin_offset, 
			li.vlf_size_mb
		FROM sys.databases d 
		CROSS APPLY sys.dm_db_log_info(d.database_id) li ),
	cte_active_vlf AS (
		SELECT db, 
			MAX(pr) as [pos]
		FROM cte_vlf
		WHERE vlf_active = 1
		GROUP BY db)
    SELECT
        d.name as [db],
		CEILING(ls.total_log_size_mb) as log_size_MB,
		CEILING(ls.active_log_size_mb) as log_used_MB,
		CEILING(ls.active_log_size_mb / NULLIF(ls.total_log_size_mb, 0) * 100) as [% used],
        NULLIF(d.log_reuse_wait_desc, N'NOTHING') as log_reuse_wait,
        d.recovery_model_desc as recovery_model,
        NULLIF(CAST(ls.log_backup_time as datetime2(0)), '1900-01-01 00:00:00') as last_translog_backup,
        mf.name,
        mf.physical_name,
        CASE mf.max_size
            WHEN 0 THEN 'Fixed'
            WHEN -1 THEN 'Illimited'
            WHEN 268435456 THEN '2 TB'
            ELSE CONCAT((mf.max_size * 8) / 1024, ' MB')
        END AS [max],
        CASE mf.growth
            WHEN 0 THEN 'Fixed'
            ELSE
                CASE mf.is_percent_growth
                    WHEN 1 THEN CONCAT(growth, '%')
                    ELSE CONCAT((mf.growth * 8) / 1024, ' MB')
                END
        END AS [growth],
		ls.total_vlf_count as vlf,
		CAST(ls.log_since_last_checkpoint_mb as decimal(38, 2)) as since_last_checkpoint_mb,
		CAST(ls.log_since_last_log_backup_mb as decimal(38, 2)) as since_last_log_backup_mb,
		CAST(ls.log_recovery_size_mb as decimal(38, 2)) as recovery_size_mb,
		av.pos as [% active position]
    FROM sys.databases d
    JOIN sys.master_files mf ON d.database_id = mf.database_id AND mf.[type] = 1 -- log
        AND mf.state <> 6 -- OFFLINE
    --OUTER APPLY (SELECT COUNT(*) as vlf FROM sys.dm_db_log_info ( d.database_id ) ) li
	CROSS APPLY sys.dm_db_log_stats( d.database_id ) ls
	LEFT JOIN cte_active_vlf av ON av.db = d.name
    WHERE d.name LIKE @database
	AND d.name NOT IN (N'master', N'model')
    ORDER BY [db]
    OPTION (MAXDOP 1);

END;
GO

Pourquoi le journal ne se vide-t-il pas ?

Le vidage du journal dépend principalement du Recovery Model (Mode de récupération) [00:07:29].

Le mode FULL

En mode de récupération Complet (FULL), SQL Server conserve toutes les transactions dans le journal jusqu’à ce qu’elles soient sauvegardées via une sauvegarde du journal de transaction (Log Backup). Si vous ne programmez pas ces sauvegardes (toutes les 5 ou 15 minutes par exemple), le journal grossira indéfiniment, même si vous faites des sauvegardes complètes de la base [00:08:53].

Le mode SIMPLE

En mode Simple, SQL Server vide automatiquement le journal lors d’un checkpoint dès que les transactions sont validées et écrites sur le disque [00:11:01]. C’est le mode recommandé pour les environnements de développement ou les bases non critiques où une perte de données depuis la dernière sauvegarde complète est acceptable.

-- Passer une base en mode Simple pour stopper la croissance infinie du log
ALTER DATABASE [MaBase] SET RECOVERY SIMPLE;

Identifier la cause du blocage

Si le journal reste plein malgré vos efforts, interrogez la vue système sys.databases pour voir la colonne log_reuse_wait_desc [00:12:54] :

Trouver la transaction coupable

Une transaction « oubliée » (un BEGIN TRAN sans COMMIT) empêche le journal de recycler son espace. Pour trouver la plus ancienne transaction active dans la base de données courante, utilisez :

DBCC OPENTRAN;

Si vous identifiez une session problématique (ex: session 54), vous pouvez obtenir plus de détails avec sp_who2 54 ou décider de la stopper [00:16:19] :

KILL 54; 

Réduire la taille physique du fichier (Shrink)

Une fois la cause résolue (sauvegarde effectuée ou mode passé en Simple), le journal contient de l’espace libre, mais le fichier sur le disque ne diminue pas automatiquement. Pour récupérer cet espace, vous devez effectuer un « Shrink » [00:17:23].

-- Réduire le fichier de log à 100 Mo
DBCC SHRINKFILE (N'MaBase_Log' , 100);

Conclusion

En production, assurez-vous d’avoir des sauvegardes de log fréquentes. Hors production, privilégiez le mode Simple. Et en cas d’urgence, gardez toujours un œil sur log_reuse_wait_desc pour comprendre qui retient votre espace disque.