// Référence des erreurs SQL Server

Erreur SQL Server 9002 : Le journal des transactions de la base de données est plein

Le fichier journal de la base de données est plein et ne peut pas s'agrandir, donc les écritures échouent. La raison indiquée dans le message (LOG_BACKUP, ACTIVE_TRANSACTION...) montre ce qui empêche la réutilisation du journal.

Numéro d'erreur
9002
Sévérité
17
Catégorie
Mémoire, journal et stockage
Message d'erreur
The transaction log for database '%ls' is full due to '%ls' and the holdup lsn is %S_LSN.

Ce que cela signifie

SQL Server écrit chaque modification dans le journal des transactions avant de modifier les données. L'espace du journal n'est réutilisé qu'une fois que plus rien n'en a besoin : dans le modèle de récupération complète, cela signifie après une sauvegarde du journal, et dans tous les modèles, la plus ancienne transaction ouverte, la réplication ou une réplique d'un groupe de disponibilité peut le retenir. Si le journal ne peut pas être réutilisé et ne peut pas s'agrandir (il a atteint sa taille maximale ou le disque est plein), chaque écriture échoue avec l'erreur 9002.

Le message indique la raison, qui est la même valeur que log_reuse_wait_desc dans sys.databases.

Causes courantes

  • LOG_BACKUP: la base de données est en mode de récupération complète ou en mode bulk-logged et les sauvegardes du journal ne sont pas effectuées.
  • ACTIVE_TRANSACTION: une transaction ouverte de longue durée ou oubliée bloque le journal.
  • REPLICATION ou AVAILABILITY_REPLICA: un lecteur de journal ou un réplica secondaire a pris du retard.
  • Le fichier journal a une taille maximale fixe ou le disque qui le contient est plein.
  • Une seule transaction énorme, telle que la suppression de millions de lignes dans une seule instruction.

Comment le corriger

  1. Voir pourquoi le journal ne peut pas être réutilisé.

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'SalesDb';
  2. Pour LOG_BACKUP, effectuez une sauvegarde du journal (et planifiez-les).

    BACKUP LOG SalesDb TO DISK = N'D:\Backup\SalesDb_log.trn';
  3. Pour ACTIVE_TRANSACTION, trouvez la transaction ouverte la plus ancienne et faites-la valider ou annuler.

    DBCC OPENTRAN (N'SalesDb');
  4. Donnez de l'espace au journal : laissez-le croître, ou ajoutez un deuxième fichier journal sur un autre disque.

    ALTER DATABASE SalesDb MODIFY FILE (NAME = SalesDb_log, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
  5. Découpez les modifications de données volumineuses en lots pour que chaque transaction reste petite.

    WHILE 1 = 1
    BEGIN
        DELETE TOP (10000) FROM dbo.EventLog WHERE CreatedAt < '2024-01-01';
        IF @@ROWCOUNT = 0 BREAK;
    END;
  6. Si vous n'avez pas besoin de restauration à un instant précis pour cette base de données, le modèle de récupération simple réutilise l'espace du journal après chaque point de contrôle.

    ALTER DATABASE SalesDb SET RECOVERY SIMPLE;

Questions

Dois-je réduire la taille du fichier journal après avoir corrigé l'erreur 9002 ?

Seulement s'il a beaucoup dépassé sa taille normale à cause d'un événement ponctuel. Réduire un journal qui a régulièrement besoin de cet espace le fera simplement croître à nouveau, ce qui prend du temps pendant que les écritures attendent.

Erreurs liées

Plus d'erreurs de mémoire, de journal et de stockage

Le code et le texte du message sont issus de Référence des événements et erreurs du moteur de base de données Microsoft. Parcourir la liste complète Liste des erreurs de SQL Server ou Rechercher toutes les erreurs de base de données.

// moins d'erreurs, corrections plus rapides

Travailler sur SQL Server dans SQLly

Un client SQL natif pour macOS, Windows et Linux avec IntelliSense conscient du schéma, des garde-fous pour les instructions risquées et des messages d’erreur clairs, pour SQL Server et les autres bases de données que vous utilisez.