Résoudre les problèmes liés à un journal des transactions saturé (erreur SQL Server 9002)Troubleshoot a Full Transaction Log (SQL Server Error 9002)

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure SQL Data Warehouse nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Cette rubrique décrit les réactions possibles et émet quelques suggestions qui vous aideront à éviter cette situation dans le futur.This topic discusses possible responses to a full transaction log and suggests how to avoid it in the future.

Quand le journal des transactions est saturé, le Moteur de base de données SQL ServerSQL Server Database Engine émet une erreur 9002.When the transaction log becomes full, Moteur de base de données SQL ServerSQL Server Database Engine issues a 9002 error. Le journal peut être renseigné quand la base de données est en ligne ou en cours de récupération.The log can fill when the database is online, or in recovery. Si le journal se remplit tandis que la base de données est en ligne, cette dernière reste en ligne et peut uniquement être lue mais pas mise à jour.If the log fills while the database is online, the database remains online but can only be read, not updated. Si le journal se remplit en cours de récupération, le Moteur de base de donnéesDatabase Engine marque la base de données comme RESOURCE PENDING.If the log fills during recovery, the Moteur de base de donnéesDatabase Engine marks the database as RESOURCE PENDING. Dans les deux cas, une intervention de l'utilisateur est nécessaire pour libérer de l'espace disque.In either case, user action is required to make log space available.

Réagir à un journal des transactions completResponding to a full transaction log

La réponse adéquate à un journal des transactions saturé dépend en partie de la ou des conditions qui ont motivé le remplissage du journal.The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill.

Pour découvrir les raisons qui empêchent de tronquer le journal dans une situation donnée, utilisez les colonnes log_reuse_wait et log_reuse_wait_desc de l’affichage catalogue sys.database.To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. Pour plus d’informations, consultez sys.databases (Transact-SQL).For more information, see sys.databases (Transact-SQL). Pour obtenir une description des facteurs susceptibles de retarder la troncation du journal, consultez Journal des transactions (SQL Server).For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).

IMPORTANTIMPORTANT!!
Si la base de données était en mode de récupération quand l’erreur 9002 s’est produite, récupérez la base de données à l’aide de l’instruction ALTER DATABASE nom_base_de_données SET ONLINE après avoir résolu le problème.If the database was in recovery when the 9002 error occurred, after resolving the problem, recover the database by using ALTER DATABASE database_name SET ONLINE.

D'autres solutions possibles en cas de saturation du journal des transactions sont les suivantes :Alternatives for responding to a full transaction log include:

  • Sauvegarde du journal.Backing up the log.

  • Libération de l'espace disque pour que le journal puisse croître automatiquement.Freeing disk space so that the log can automatically grow.

  • Déplacement du fichier journal vers une unité dotée d'un espace disque suffisant.Moving the log file to a disk drive with sufficient space.

  • Augmentation de la taille du fichier journal.Increasing the size of a log file.

  • Ajout d'un fichier journal sur un autre disque.Adding a log file on a different disk.

  • Achèvement ou suppression d'une transaction longue.Completing or killing a long-running transaction.

Ces solutions sont abordées dans les sections qui suivent.These alternatives are discussed in the following sections. Optez pour une solution adaptée à votre situation.Choose a response that fits your situation best.

Sauvegarder le journalBack up the log

Si vous travaillez en mode de restauration complète ou en mode de récupération utilisant les journaux de transactions et si vous n'avez pas sauvegardé récemment le journal des transactions, la création d'une sauvegarde est ce qui empêche la troncation du journal.Under the full recovery model or bulk-logged recovery model, if the transaction log has not been backed up recently, backup might be what is preventing log truncation. Si le journal n’a jamais été sauvegardé, vous devez créer deux sauvegardes du journal pour autoriser le Moteur de base de donnéesDatabase Engine à le tronquer à l’endroit exact de la dernière sauvegarde.If the log has never been backed up, you must create two log backups to permit the Moteur de base de donnéesDatabase Engine to truncate the log to the point of the last backup. Le fait de tronquer le journal permet de libérer de l'espace pour les nouveaux enregistrements de ce dernier.Truncating the log frees space for new log records. Pour empêcher le journal de se remplir à nouveau, effectuez les sauvegardes régulièrement.To keep the log from filling up again, take log backups frequently.

Pour créer une sauvegarde du journal des transactionsTo create a transaction log backup

IMPORTANTIMPORTANT
Si la base de données est endommagée, consultez Sauvegardes de la fin du journal (SQL Server).If the database is damaged, see Tail-Log Backups (SQL Server).

Libération d’espace disqueFreeing disk space

Vous pouvez libérer de l'espace sur le disque où est stocké le fichier journal des transactions de la base de données en supprimant ou en déplaçant d'autres fichiers.You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. Ceci permet au système de récupération d'augmenter automatiquement la taille du fichier journal.The freed disk space allows the recovery system to enlarge the log file automatically.

Déplacer le fichier journal vers un autre disqueMove the log file to a different disk

Si vous ne pouvez pas libérer suffisamment d'espace disque sur le lecteur où le fichier journal se trouve actuellement, essayez de déplacer ce fichier sur une autre unité disposant d'espace suffisant.If you cannot free enough disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space.

IMPORTANTIMPORTANT!! Les fichiers journaux ne doivent jamais être placés sur des systèmes de fichiers compressés.Log files should never be placed on compressed file systems.

Déplacer un fichier journalMove a log file

Augmenter la taille d’un fichier journalIncrease log file size

Si le disque du journal dispose d'espace libre, vous pouvez augmenter la taille du fichier journal.If space is available on the log disk, you can increase the size of the log file. La taille maximale pour les fichiers journaux est de deux téraoctets (To) par fichier journal.The maximum size for log files is two terabytes (TB) per log file.

Augmenter la taille du fichierIncrease the file size

Si la fonctionnalité de croissance automatique est désactivée, que la base de données est en ligne et que l'espace disque disponible est suffisant, effectuez l'une des opérations suivantes :If autogrow is disabled, the database is online, and sufficient space is available on the disk, either:

  • Augmentez manuellement la taille du fichier pour générer un seul incrément de croissance.Manually increase the file size to produce a single growth increment.

  • Activez la croissance automatique à l'aide de l'instruction ALTER DATABASE pour définir un incrément de croissance différent de zéro pour l'option FILEGROWTH.Turn on autogrow by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.

REMARQUE Dans les deux cas, si la limite de taille actuelle est atteinte, augmentez la valeur MAXSIZE.NOTE In either case, if the current size limit has been reached, increase the MAXSIZE value.

Ajout d’un fichier journal sur un autre disqueAdd a log file on a different disk

Ajoutez un nouveau fichier journal à la base de données d'un autre disque doté d'un espace suffisant à l'aide de l'instruction ALTER DATABASE <nom_base_de_données> ADD LOG FILE.Add a new log file to the database on a different disk that has sufficient space by using ALTER DATABASE <database_name> ADD LOG FILE.

Ajouter un fichier journalAdd a log file

Achever ou supprimer une transaction longueComplete or kill a long-running transaction

Découverte des transactions de longue duréeDiscovering long-running transactions

Une transaction de très longue durée entraîne la saturation du journal des transactions.A very long-running transaction can cause the transaction log to fill. Pour rechercher des transactions de longue durée, appliquez une des procédures suivantes :To look for long-running transactions, use one of the following:

Supprimer une transactionKill a transaction

Parfois, il vous suffit de mettre un terme au processus ; vous pouvez avoir à utiliser l’instruction KILL .Sometimes you just have to end the process; you may have to use the KILL statement. Utilisez cette instruction avec précaution, particulièrement lorsque des processus critiques que vous ne voulez pas supprimer sont en cours d’exécution.Please use this statement very carefully, especially when critical processes are running that you don't want to kill. Pour plus d’informations, voir KILL (Transact-SQL).For more information, see KILL (Transact-SQL)

Voir aussiSee also

Article de support de la base de connaissances - Le journal des transactions augmente de manière inattendue ou est saturé sur un ordinateur SQL Server ALTER DATABASE (Transact-SQL) KB support article - A transaction log grows unexpectedly or becomes full in SQL Server ALTER DATABASE (Transact-SQL)
Gérer la taille du fichier journal des transactions Manage the Size of the Transaction Log File
Sauvegardes des journaux de transactions (SQL Server) Transaction Log Backups (SQL Server)
sp_add_log_file_recover_suspect_db (Transact-SQL)sp_add_log_file_recover_suspect_db (Transact-SQL)