Transaction log cannot be truncated

PROBLEM:

You cannot truncate SQL Server transaction log

SOLUTION:

If you are wondering why a transaction log cannot be truncated, exeuting the following query can assist

select name, log_reuse_wait_desc from sys.databases

The following briefly describes the values of the log_reuse_wait_desc column:

• NOTHING - Currently there are one or more reusable log files. This is the normal state that means you can do a log truncation
• Wait 15 min, try again. If you still cannot truncate, restart the server
• CHECKPOINT - No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). This is a routine reason for delaying log truncation
• Do a manual checkpoint
• LOG_BACKUP - A log backup is required to move the head of the log forward (full or bulk-logged recovery models only)
• You need to make two backups to actually free the space
• ACTIVE_BACKUP_OR_RESTORE - A data backup or a restore is in progress (all recovery models). A data backup works like an active transaction, and, when running, the backup prevents truncation
• Wait until it ends
• ACTIVE_TRANSACTION - A transaction is active (all recovery models). A long-running transaction might exist at the start of the log backup
• Do a log backup
• DATABASE_MIRRORING - Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only)
• Configure the mirroring
• REPLICATION - During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only)
• Wait until it ends
• DATABASE_SNAPSHOT_CREATION - A database snapshot is being created (all recovery models). This is a routine, and typically brief
• Wait until it ends
• LOG_SCAN - A log scan is occurring (all recovery models). This is a routine, and typically brief
• Wait until it ends
• AVAILABILITY_REPLICA - To identify which secondary database is delaying log truncation, see the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view
• OTHER_TRANSIENT - This value is currently not used

After resolving the reason that is shown, if you are using full recovery model then perform a log backup to truncate the log file, and then use DBSS SHRINKFILE to reduce the filesize of the log file.

USE [db_name]
GO
DBCC SHRINKFILE(N'db_name_log', 1)
BACKUP LOG [db_name]
DBCC SHRINKFILE(N'db_name_log', 1)
GO

If the log file does not reduce in size when using DBCC SHRINKFILE as part of the steps above, the active part of the log file must have been at the end of the log file at that point in time. You can correct that situation by generating enough dummy transactions to cause SQL Server to switch back to the beginning of the log file. You should then attempt the truncation again.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!