Détecter un problème lié à un journal des transactions saturé (erreur de serveur SQL 9002)

S'applique à : SQL Server

Cet article s'applique aux instances SQL Server.

Option 1 : Exécuter les étapes directement dans un notebook exécutable via Azure Data Studio

Option 2 : Suivre l'étape manuellement

Cet article décrit les réactions possibles à un journal des transactions saturé et émet quelques suggestions qui vous permettront d'éviter cette situation à l'avenir.

Quand le journal des transactions est saturé, le moteur de base de données SQL Server émet une erreur 9002. Le journal peut être renseigné quand la base de données est en ligne ou en cours de récupération. Si le journal se remplit tandis que la base de données est en ligne, la base de données reste en ligne mais elle peut uniquement être lue, et pas être mise à jour. Si le journal se remplit en cours de récupération, le moteur de base de données marque la base de données comme RESOURCE EN ATTENTE. Dans les deux cas, une intervention de l'utilisateur est nécessaire pour libérer de l'espace disque.

Raisons courantes d’un journal des transactions saturé

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. Les causes courantes sont les suivantes :

• Journal non tronqué
• Volume de disque plein
• La taille du journal est définie sur une valeur maximale fixe ou la croissance automatique est désactivée
• Synchronisation de la réplication ou du groupe de disponibilité qui ne peut pas se terminer

Comment résoudre un journal des transactions plein

Les étapes spécifiques suivantes vous aident à trouver la raison d’un journal des transactions saturé et à résoudre le problème.

1. Tronquer le journal

Une solution très courante à ce problème consiste à s’assurer que les sauvegardes du journal des transactions sont effectuées pour votre base de données, ce qui permet de garantir que le journal est tronqué. Si aucun historique récent du journal des transactions n’est indiqué pour la base de données avec un journal des transactions saturé, la solution au problème est simple : reprendre les sauvegardes régulières du journal des transactions de la base de données.

Pour plus d’informations, consultez Gérer la taille du journal et Réduire un fichier.

Explication de la troncation du journal

Il y a une différence entre tronquer un journal des transactions et réduire un journal des transactions. La troncation du journal se produit normalement pendant une sauvegarde du journal des transactions, et c’est une opération logique qui supprime les enregistrements validés dans le journal, tandis que la réduction du journal libère de l’espace physique sur le système de fichiers en réduisant la taille du fichier. La troncation du journal se produit sur une limite de fichier journal virtuel (VLF) et un fichier journal peut contenir plusieurs fichiers journaux virtuels. Un fichier journal ne peut être réduit que s'il y a de l'espace vide à l'intérieur du fichier journal à récupérer. La réduction d’un fichier journal seul ne peut pas résoudre le problème d’un fichier journal saturé. Au lieu de cela, vous devez découvrir pourquoi le fichier journal est saturé et ne peut pas être tronqué.

Qu’est-ce qui empêche la troncation du journal ?

Pour découvrir ce qui empêche la troncation du journal dans un cas donné, reportez-vous aux colonnes log_reuse_wait et log_reuse_wait_desc de la vue catalogue sys.databases. Pour plus d’informations, consultez sys.databases (Transact-SQL). Pour obtenir une description des facteurs susceptibles de retarder la troncation du journal, consultez Journal des transactions (SQL Server).

L’ensemble suivant de commandes T-SQL vous aide à identifier si le journal des transactions d’une base de données n’est pas tronqué et à en déterminer la raison. Le script suivant recommande également les étapes de résolution du problème :

SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc varchar (24)

IF ( OBJECT_id (N'tempdb..#CannotTruncateLog_Db') is not null)
BEGIN
    DROP TABLE #CannotTruncateLog_Db
END

--get info about transaction logs in each database.

IF ( OBJECT_id (N'tempdb..#dm_db_log_space_usage') is not null)
BEGIN
    DROP TABLE #dm_db_log_space_usage
END
SELECT * INTO #dm_db_log_space_usage FROM sys.dm_db_log_space_usage where 1=0

DECLARE log_space CURSOR FOR SELECT NAME FROM sys.databases
OPEN log_space

FETCH NEXT FROM log_space into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

    set @SQL = '
    insert into #dm_db_log_space_usage (
    database_id,
    total_log_size_in_bytes,
    used_log_space_in_bytes,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup
    )
    select
    database_id,
    total_log_size_in_bytes,
    used_log_space_in_bytes,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup
    from ' + @dbname +'.sys.dm_db_log_space_usage'

    BEGIN TRY
        exec (@SQL)
    END TRY

    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    FETCH NEXT FROM log_space into @dbname
END

CLOSE log_space
DEALLOCATE log_space

--select the affected databases
SELECT
    sdb.name as DbName,
    sdb.log_reuse_wait, sdb.log_reuse_wait_desc,
    log_reuse_wait_explanation = CASE

        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a defferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
    ELSE 'None' END,

    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024 as Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024 as Total_log_size_MB,
    100 - lsu.used_log_space_in_percent as Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb INNER JOIN #dm_db_log_space_usage lsu ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0

SELECT * FROM #CannotTruncateLog_Db

DECLARE no_truncate_db CURSOR FOR
    SELECT log_reuse_wait, log_reuse_wait_desc, DbName, database_id, recovery_model_desc FROM #CannotTruncateLog_Db;

OPEN no_truncate_db

FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

WHILE @@FETCH_STATUS = 0
BEGIN
    if (@log_reuse_wait > 0)
        select '-- ''' + @dbname +  ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --'  as 'Individual Database Report'

    if (@log_reuse_wait = 1)
    BEGIN
        select 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' as Recommendation
        select 'USE ''' + @dbname+ '''; CHECKPOINT' as CheckpointCommand
        select 'select * from sys.dm_db_log_info(' + CONVERT(varchar,@database_id)+ ')' as VLF_LogInfo
    END
    else if (@log_reuse_wait = 2)
    BEGIN
        select 'Is '+ @recovery_model_desc +' recovery model the intended choice for ''' + @dbname+ ''' database? Review recovery models and determine if you need to change it. https://video2.skills-academy.com/sql/relational-databases/backup-restore/recovery-models-sql-server' as RecoveryModelChoice
        select 'To truncate the log consider performing a transaction log backup on database ''' + @dbname+ ''' which is in ' + @recovery_model_desc +' recovery model. Be mindful of any existing log backup chains that could be broken' as Recommendation
        select 'BACKUP LOG [' + @dbname + '] TO DISK = ''some_volume:\some_folder\' + @dbname + '_LOG.trn ''' as BackupLogCommand
    END
    else if (@log_reuse_wait = 3)
    BEGIN
        select 'Either wait for or cancel any active backups currently running for database ''' +@dbname+ '''. To check for backups, run this command:' as Recommendation
        select 'select * from sys.dm_exec_requests where command like ''backup%'' or command like ''restore%''' as FindBackupOrRestore
    END
    else if (@log_reuse_wait = 4)
    BEGIN
        select 'Active transactions currently running  for database ''' +@dbname+ '''. To check for active transactions, run these commands:' as Recommendation
        select 'DBCC OPENTRAN (''' +@dbname+ ''')' as FindOpenTran
        select 'select database_id, db_name(database_id) dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id from sys.dm_tran_database_transactions dbtran left outer join sys.dm_tran_session_transactions stran on dbtran.transaction_id = stran.transaction_id where database_id = ' + CONVERT(varchar, @database_id) as FindOpenTransAndSession
    END

    else if (@log_reuse_wait = 5)
    BEGIN
        select 'Database Mirroring for database ''' +@dbname+ ''' is behind on synchronization. To check the state of DBM, run the commands below:' as Recommendation
        select 'select db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc from sys.database_mirroring where mirroring_guid is not null and mirroring_state <> 4 and database_id = ' + convert(sysname, @database_id)  as CheckMirroringStatus

        select 'Database Mirroring for database ''' +@dbname+ ''' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' as Recommendation
        select 'exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ ''', 5, 0; waitfor delay ''00:01:01''; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ '''; exec msdb.sys.sp_dbmmonitordropmonitoring'   as CheckMirroringStatusAnd
    END

    else if (@log_reuse_wait = 6)
    BEGIN
        select 'Replication transactions still undelivered from publisher database ''' +@dbname+ ''' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encoutered any errors' as Recommendation
        select 'DBCC OPENTRAN  (''' + @dbname + ''')' as CheckOldestNonDistributedTran
        select 'select top 5 * from distribution..MSlogreader_history where runstatus in (6, 5) or error_id <> 0 and agent_id = find_in_mslogreader_agents_table  order by time desc ' as LogReaderAgentState
    END

    else if (@log_reuse_wait = 9)
    BEGIN
        select 'Always On transactions still undelivered from primary database ''' +@dbname+ ''' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' as Recommendation
        select 'select availability_group=cast(ag.name as varchar(30)), primary_replica=cast(ags.primary_replica as varchar(30)),primary_recovery_health_desc=cast(ags.primary_recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ags.synchronization_health_desc as varchar(30)),ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc=cast(ag.automated_backup_preference_desc as varchar(10))  from sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' as CheckAGHealth
        select 'SELECT  group_name=cast(arc.group_name as varchar(30)), replica_server_name=cast(arc.replica_server_name as varchar(30)), node_name=cast(arc.node_name as varchar(30)),role_desc=cast(ars.role_desc as varchar(30)), ar.availability_mode_Desc, operational_state_desc=cast(ars.operational_state_desc as varchar(30)), connected_state_desc=cast(ars.connected_state_desc as varchar(30)), recovery_health_desc=cast(ars.recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ars.synchronization_health_desc as varchar(30)), ars.last_connect_error_number, last_connect_error_description=cast(ars.last_connect_error_description as varchar(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc=cast(ar.primary_role_allow_connections_desc as varchar(30)) from sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY cast(arc.group_name as varchar(30)), cast(ars.role_desc as varchar(30))' as CheckReplicaHealth
        select 'select database_name=cast(drcs.database_name as varchar(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local,drcs.is_failover_ready,drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc=cast(drs.suspend_reason_desc as varchar(30)), synchronization_state_desc=cast(drs.synchronization_state_desc as varchar(30)), synchronization_health_desc=cast(drs.synchronization_health_desc as varchar(30)), database_state_desc=cast(drs.database_state_desc as varchar(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time,drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time from sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' as LogMovementHealth
        select 'For more information see https://video2.skills-academy.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' as OnlineDOCResource
    END
    else if (@log_reuse_wait in (10, 11, 12, 14))
    BEGIN
        select 'This state is not documented and is expected to be rare and short-lived' as Recommendation
    END
    else if (@log_reuse_wait = 13)
    BEGIN
        select 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' as Finding
        select 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' as Recommendation
        select 'ALTER DATABASE [' +@dbname+ '] SET TARGET_RECOVERY_TIME = 0 SECONDS' as DisableIndirectCheckpointTemporarily
    END
    else if (@log_reuse_wait = 16)
    BEGIN
        select 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' as Finding
        select 'Review https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/' as ReviewBlog
        select 'use ' +@dbname+ ' CHECKPOINT' as RunCheckpoint
    END

    FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

END

CLOSE no_truncate_db
DEALLOCATE no_truncate_db

LOG_BACKUP log_reuse_wait

L’action la plus courante à envisager si vous voyez LOG_BACKUP ou log_reuse_wait consiste à passer en revue le mode de récupération de votre base de données et à sauvegarder le journal des transactions de votre base de données.

Prendre en compte le modèle de récupération de la base de données

Il est possible que le journal des transactions ne puisse pas être tronqué avec la catégorie LOG_BACKUPlog_reuse_wait, car vous ne l’avez jamais sauvegardé. Dans la plupart des cas, votre base de données utilise le mode de récupération FULL ou BULK_LOGGED, mais vous n’avez pas sauvegardé votre journal des transactions. Vous devez examiner attentivement chaque mode de récupération de base de données : effectuez des sauvegardes régulières de fichier journal sur toutes les bases de données en mode de récupération FULL ou BULK LOGGED pour réduire les occurrences de l’erreur 9002. Pour plus d’informations, consultez Modes de récupération.

Sauvegarder le journal

Sous les modes de récupération FULL ou BULK_LOGGED, 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. Vous devez sauvegarder le journal des transactions pour permettre la publication des enregistrements de journal et la troncation du journal. Si le journal n'a jamais été sauvegardé, vous devez en créer deux sauvegardes pour permettre au moteur de base de données de le tronquer à l'endroit exact de la dernière sauvegarde. Le fait de tronquer le journal permet de libérer de l'espace pour les nouveaux enregistrements de ce dernier. Pour empêcher le journal de se remplir à nouveau, effectuez les sauvegardes régulièrement et plus fréquemment. Pour plus d’informations, consultez Modes de récupération.

Un historique complet de toutes les opérations de restauration et de sauvegarde SQL Server sur une instance de serveur est stocké dans la base de données système msdb. Pour consulter l’historique de sauvegarde complet d’une base de données, utilisez l’exemple de script suivant :

SELECT bs.database_name
, backuptype = CASE
    WHEN bs.type = 'D' and bs.is_copy_only = 0 THEN 'Full Database'
    WHEN bs.type = 'D' and bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
    WHEN bs.type = 'I' THEN 'Differential database backup'
    WHEN bs.type = 'L' THEN 'Transaction Log'
    WHEN bs.type = 'F' THEN 'File or filegroup'
    WHEN bs.type = 'G' THEN 'Differential file'
    WHEN bs.type = 'P' THEN 'Partial'
    WHEN bs.type = 'Q' THEN 'Differential partial' END + ' Backup'
, bs.recovery_model
, BackupStartDate = bs.Backup_Start_Date
, BackupFinishDate = bs.Backup_Finish_Date
, LatestBackupLocation = bf.physical_device_name
, backup_size_mb = bs.backup_size/1024./1024.
, compressed_backup_size_mb = bs.compressed_backup_size/1024./1024.
, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
, checkpoint_lsn
, begins_log_chain
FROM msdb.dbo.backupset AS bs
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model in ('FULL', 'BULK-LOGGED')
AND bs.backup_start_date > DATEADD(month, -2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name asc, bs.Backup_Start_Date desc;

Un historique complet de toutes les opérations de restauration et de sauvegarde SQL Server sur une instance de serveur est stocké dans la base de données système msdb. Pour plus d’informations sur l’historique de sauvegarde, consultez Historique de sauvegarde et informations d’en-tête (SQL Server).

Créer une sauvegarde de fichier journal

Exemple de procédure de sauvegarde du journal :

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn'

• Sauvegarder un journal des transactions (SQL Server)

• SqlBackup (SMO)

ACTIVE_TRANSACTION log_reuse_wait

Les étapes de résolution des problèmes pour la raison ACTIVE_TRANSACTION incluent la découverte de la transaction durable et sa résolution (dans certains cas, à l’aide de la commande KILL).

Découvrir des transactions de longue durée

Une transaction de très longue durée entraîne la saturation du journal des transactions. Pour rechercher des transactions de longue durée, appliquez une des procédures suivantes :

• sys.dm_tran_database_transactions.

Cet affichage de gestion dynamique retourne des informations sur les transactions au niveau de la base de données. Pour une transaction de longue durée, les colonnes particulièrement intéressantes incluent l’heure du premier enregistrement de journal (database_transaction_begin_time), l’état actuel de la transaction (database_transaction_state)et le numéro séquentiel dans le journal de l’enregistrement BEGIN dans le journal des transactions (database_transaction_begin_lsn).

• DBCC OPENTRAN. Cette instruction vous permet d'identifier l'ID du propriétaire de la transaction et éventuellement de retrouver la source de la transaction pour y mettre fin dans les règles de l'art (la valider au lieu de la restaurer).

Supprimer une transaction

Parfois, il suffit d’arrêter la transaction. Vous devez éventuellement utiliser l’instruction KILL. Utilisez l’instruction KILL avec une extrême prudence, en particulier quand des processus critiques que vous ne voulez pas arrêter sont en cours d’exécution.

CHECKPOINT log_reuse_wait

Aucun point de contrôle n'est apparu depuis la dernière troncation du journal ou le début du journal n'est pas encore allé au-delà d'un fichier journal virtuel. (Tous les modes de récupération)

Il s'agit d'une raison courante de retarder la troncation du journal. En cas de retard, envisagez d’exécuter la commande CHECKPOINT sur la base de données ou d’examiner les fichiers journaux virtuels (VLF).

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

AVAILABILITY_REPLICA log_reuse_wait

Quand les modifications de transaction au niveau du réplica du groupe de disponibilité Always On principal ne sont pas encore renforcées sur le réplica secondaire, le journal des transactions du réplica principal ne peut pas être tronqué. La taille du journal peut alors augmenter. Cela peut se produire peu importe que le réplica secondaire soit défini pour le mode de validation synchrone ou asynchrone. Pour plus d’informations sur la résolution de ce type de problème, consultez Erreur 9002. Le journal des transactions de la base de données est saturé en raison de l’erreur AVAILABILITY_REPLICA.

Réplication, suivi des modifications ou CDC

Les fonctionnalités telles que la réplication, le suivi des modifications et la capture des changements de données (CDC) s’appuient sur le journal des transactions. Par conséquent, si des transactions ou des modifications ne sont pas remises, cela peut empêcher la troncation du journal des transactions.

Utilisez DBCC OPENTRAN, le moniteur de réplication ou les procédures stockées pour le suivi des modifications et la capture des changements de données (CDC) pour examiner et résoudre les problèmes liés à ces fonctionnalités.

Pour plus d’informations sur les facteurs log_reuse_wait

Pour plus de détails, consultez Facteurs pouvant retarder la troncation du journal.

2. Résoudre le volume de disque complet

Dans certains cas, le volume de disque qui héberge le fichier journal de transactions peut être rempli. Vous pouvez effectuer l’une des actions suivantes pour résoudre le scénario journal complet qui résulte d’un disque complet :

Espace disque disponible

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. Ceci permet au système de récupération d'augmenter automatiquement la taille du fichier journal.

Déplacer le fichier journal vers un autre disque

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.

Consultez Déplacer des fichiers de base de données pour plus d’informations sur la façon de modifier l’emplacement d’un fichier journal.

Ajout d’un fichier journal sur un autre disque

Ajoutez un nouveau fichier journal à la base de données sur un autre disque disposant d’un espace suffisant à l’aide de ALTER DATABASE <database_name> ADD LOG FILE. Plusieurs fichiers journaux d’une base de données unique doivent être considérés comme une condition temporaire pour résoudre un problème d’espace, et non une condition à long terme. La plupart des bases de données ne doivent avoir qu’un seul fichier journal des transactions. Continuez à examiner la raison pour laquelle le journal des transactions est plein et ne peut pas être tronqué. Envisagez d’ajouter des fichiers journaux de transactions supplémentaires temporaires seulement en tant qu’étape de dépannage avancée.

Pour plus d’informations, consultez Ajouter des fichiers de données ou journaux à une base de données.

Script de l’utilitaire pour les actions recommandées

Ces étapes peuvent être partiellement automatisées en exécutant le script T-SQL suivant pour identifier les fichiers journaux qui utilisent un pourcentage important d’espace disque et suggèrer des actions :

DECLARE @log_reached_disk_size BIT = 0

SELECT
    name LogName,
    physical_name,
    CONVERT(bigint, size)*8/1024 LogFile_Size_MB,
    volume_mount_point,
    available_bytes/1024/1024 Available_Disk_space_MB,
    (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 file_size_as_percentage_of_disk_space,
    db_name(mf.database_id) DbName
FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
ORDER BY size DESC

if @@ROWCOUNT > 0
BEGIN

    set @log_reached_disk_size = 1

    -- Discover if any logs have are close to or completely filled disk volume they reside on.
    -- Either Add A New File To A New Drive, Or Shrink Existing File
    -- If Cannot Shrink, Go To Cannot Truncate Section

    DECLARE @db_name_filled_disk sysname, @log_name_filled_disk sysname, @go_beyond_size bigint

    DECLARE log_filled_disk CURSOR FOR
        SELECT
            db_name(mf.database_id),
            name
        FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
        WHERE mf.[type_desc] = 'LOG'
            AND (convert(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
        ORDER BY size desc

    OPEN log_filled_disk

    FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding
        SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk +'" transaction log file size or add a new file to a NEW volume' AS Recommendation
        SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command
        SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile
        SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://video2.skills-academy.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst
        SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace

         FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    END

    CLOSE log_filled_disk
    DEALLOCATE log_filled_disk

END

3. Modifier la limite de taille du journal ou activer la croissance automatique

L’erreur 9002 peut être générée si la taille du journal des transactions a été définie sur une limite supérieure ou si la fonctionnalité de croissance automatique n’est pas autorisée. Dans ce cas, l’activation de la croissance automatique ou l’augmentation manuelle de la taille du journal peut aider à résoudre le problème. Utilisez cette commande T-SQL pour rechercher ces fichiers journaux et suivez les recommandations fournies :

SELECT DB_NAME(database_id) DbName,
       name LogName,
       physical_name,
       type_desc ,
       CONVERT(bigint, SIZE)*8/1024 LogFile_Size_MB ,
       CONVERT(bigint,max_size)*8/1024 LogFile_MaxSize_MB ,
       (SIZE*8.0/1024)/(max_size*8.0/1024)*100 percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END as AutoGrow
FROM sys.master_files
WHERE file_id = 2
    AND (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
    AND max_size not in (-1, 268435456)
    OR growth = 0

if @@ROWCOUNT > 0
BEGIN
    DECLARE @db_name_max_size sysname, @log_name_max_size sysname, @configured_max_log_boundary bigint, @auto_grow int

    DECLARE reached_max_size CURSOR FOR
        SELECT db_name(database_id),
               name,
               CONVERT(bigint, SIZE)*8/1024,
               growth
        FROM sys.master_files
        WHERE file_id = 2
            AND ( (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
            AND max_size not in (-1, 268435456)
            OR growth = 0 )

    OPEN reached_max_size

    FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @auto_grow = 0
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' as Finding
            SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' as Recommendation
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' as AutoGrowth
          END
        ELSE
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + convert(varchar(24), @configured_max_log_boundary) + ' MB and this limit has been reached!' as Finding
            SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' as Recommendation
          END

        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' as UnlimitedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT(varchar(24), @configured_max_log_boundary) +'MB )' as IncreasedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' as AddNewFile

        FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    END

    CLOSE reached_max_size
    DEALLOCATE reached_max_size
END
ELSE
    SELECT 'Found no files that have reached max log file size' as Findings

Augmenter la taille du fichier journal ou activer la croissance automatique

Si le disque du journal dispose d'espace libre, vous pouvez augmenter la taille du fichier journal. La taille maximale pour les fichiers journaux est de 2 téraoctets (To) par fichier journal.

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 les étapes suivantes :

• Augmentez manuellement la taille du fichier pour générer un seul incrément de croissance. Il s’agit de recommandations générales sur la taille et la croissance du journal.
• 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. Consultez Considérations relatives aux paramètres de croissance automatique et de réduction automatique dans SQL Server.

Contenu connexe

• ALTER DATABASE (Transact-SQL)
• Gérer la taille du fichier journal des transactions
• Sauvegardes du journal des transactions (SQL Server)
• sp_add_log_file_recover_suspect_db (Transact-SQL)
• MSSQLSERVER_9002
• Comment la structure de fichier journal peut affecter le temps de récupération de la base de données – Microsoft Tech Community