# Comprendre et gérer le journal de transactions SQL Server

> Journal de transaction plein : lire log_reuse_wait_desc, choisir le mode de récupération, trouver la transaction ouverte, réduire le fichier.
>
> Source : https://www.pachadata.com/docs/howtos/administration/log/
> Auteur : Rudi Bruchez · publié le 2026-02-11 · mis à jour le 2026-10-14
> Version Markdown de la page, pour les outils et les LLM. La page HTML fait référence.


>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](http://www.youtube.com/watch?v=-m5WsTQ-aBE).

> TL;DR :
>
> Un journal de transaction qui sature le disque est souvent dû à une mauvaise configuration ou à une transaction oubliée. Les points clés à retenir :
> 
> - **Vérifiez le mode de récupération** : Si vous êtes en mode `FULL`, vous **devez** faire des sauvegardes de log régulières pour vider le journal [00:08:53].
> - **Identifiez les blocages** : Utilisez `sys.databases` (colonne `log_reuse_wait_desc`) pour savoir pourquoi le log ne se vide pas [00:12:54].
> - **Gérez les transactions longues** : Une transaction ouverte empêche la rotation du journal [00:14:40].
> - **Réduisez avec parcimonie** : N'utilisez `DBCC SHRINKFILE` qu'après avoir résolu la cause racine du remplissage [00:17:23].


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 :

```sql
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.


Procédure sp_log_space pour un audit détaillé (https://github.com/rudi-bruchez/tsql-scripts/blob/main/stored-procedures/sp_logspace.sql) :

```sql
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.

```sql
-- Passer une base en mode Simple pour stopper la croissance infinie du log
ALTER DATABASE [PachaDataFormation] 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] :

| Valeur | Signification | Action requise |
| --- | --- | --- |
| **NOTHING** | Tout va bien | Aucune |
| **LOG_BACKUP** | Attente de sauvegarde | Lancez une sauvegarde de log (en mode FULL) [00:13:24] |
| **ACTIVE_TRANSACTION** | Transaction ouverte | Identifiez et terminez la session bloquante [00:14:40] |
| **REPLICATION** | Attente réplication | Vérifiez l'état de votre topologie de réplication |
| **AVAILABILITY_REPLICA** | Always On AG | Vérifiez l'état de votre réplica secondaire |

### 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 :

```sql
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] :

```sql
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].

```sql
USE [PachaDataFormation];

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

> Attention :
>
> **Le journal est séquentiel.** Si la portion "active" du journal se trouve à la fin du fichier physique au moment de la commande, le `SHRINK` ne pourra pas réduire la taille immédiatement [00:18:59].
> Dans ce cas, attendez un peu que la rotation du log s'effectue naturellement et relancez la commande plus tard.


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



---

## L'auteur

Rudi Bruchez, consultant SQL Server indépendant depuis 2006 : audit de performance, optimisation, migrations, haute disponibilité et formation. Pour un audit ou une intervention sur votre serveur : https://www.pachadata.com/contact/ (audit à distance : https://www.pachadata.com/services/audit-sql-server-a-distance/).
