Journal de transactions SQL Server qui grossit : causes et remèdes.

Comment fonctionne le journal

Le journal enregistre toutes les modifications. Son espace est réutilisé une fois que les enregistrements ne sont plus nécessaires. En modèle de récupération complet (FULL) ou journalisé en bloc (BULK_LOGGED), cela exige une sauvegarde du journal de transactions. En modèle simple (SIMPLE), c'est le point de contrôle (checkpoint) qui libère l'espace. Si rien ne permet cette libération, le fichier grossit jusqu'à saturer le disque.

Trouver la cause

Une requête suffit pour savoir ce qui retient le journal :

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases;

La colonne log_reuse_wait_desc indique la raison :

• LOG_BACKUP : aucune sauvegarde du journal n'est effectuée, ou elle échoue.
• ACTIVE_TRANSACTION : une transaction longue est restée ouverte.
• REPLICATION : la réplication n'a pas lu les enregistrements du journal.
• AVAILABILITY_REPLICA : un réplica Always On est en retard ou injoignable.
• DATABASE_MIRRORING : la mise en miroir est en retard.

Les remèdes selon la cause

• LOG_BACKUP : planifier des sauvegardes de journal régulières, à une fréquence compatible avec la perte de données acceptable. Si la restauration à un instant précis n'est pas nécessaire, passer en modèle simple est une décision à prendre avec le métier.
• ACTIVE_TRANSACTION : repérer la transaction (DBCC OPENTRAN, vues de gestion dynamique), puis corriger l'application. Mettre fin à une session de force peut déclencher une annulation (rollback) très longue.
• Réplication ou Always On : rétablir le flux, relancer l'agent ou le réplica en retard.

Faut-il réduire le fichier ?

Une fois la cause traitée, on peut réduire le fichier journal, une fois, à une taille raisonnable. Il ne faut pas planifier de réduction régulière : le fichier regrossira, et chaque croissance est coûteuse. On préfère une taille initiale adaptée à l'activité et une croissance en taille fixe plutôt qu'en pourcentage, car des croissances trop petites créent un grand nombre de segments internes (VLF) qui ralentissent le démarrage et les restaurations.

Quand le disque est déjà plein

• Libérer de la place ailleurs sur le volume, ou ajouter temporairement un disque.
• Ajouter un fichier journal supplémentaire sur un autre volume, en attendant de traiter la cause.
• Ne jamais supprimer le fichier .ldf : la base deviendrait inutilisable.

Prévenir

Des alertes sur l'espace disque et sur log_reuse_wait_desc, des sauvegardes de journal supervisées et testées, et une taille de journal calibrée sur les plus gros traitements (chargements, reconstructions d'index).