If you regularly backup your sql server transaction log of your database that uses full recovery model, it truncate the Transaction Log file after each log backup under normal conditions. Thus, this prevents the transaction log file from growing. You can see the details in docs.microsoft.com
If your sql server log file not shrinking after backup this article will help you.
In addition a friend asked me a question? Does sql server full backup truncate the log?
Short answer is no, sql server full backup does not truncate log.
If CDC(Change Data Capture) or replication is enabled in your database, SQL Server will not truncate the transaction log file or you will not be able to shrink the transaction log file after backing up the Transaction Log, and this start filling your disks.
Why SQL Server does not truncate transaction log or you can not shrink log file?
Normally, the log file may not be truncated after a backup for another reason. We can learn why the log file is not truncated using the following query.
|
1 2 3 |
USE mydatabase GO select log_reuse_wait_desc from sys.databases where database_id=DB_ID() |
If the log_reuse_wait_desc is LOG_BACKUP, this means that transaction log is not backed up. To shrink the log file, you should backup your transaction log one or more times.
If you are using the full recovery model, you should create a job to backup transaction logs on a regular basis. You may want to read the article titled “Database Recovery Models“.
If the log_reuse_wait_desc is replication and you can not shrink log, and no replication has been configured on the database, then this means that CDC is enabled in your database. If you use replication and see this error you should check the replication status. Probably there are some errors about replication and SQL Server does not truncate the transaction log due to hold the changed data in the transaction log. That’s why fix the error about replication to solve this error.
Disable CDC
To disable the CDC, you must use the following command.
|
1 2 3 4 |
USE mydatabase GO EXEC sys.sp_cdc_disable_db GO |
After disabling the CDC, you will not be able to shrink the log file for a while.
This is because the CHECKPOINT process has not yet been triggered.
When you execute the above query again after the CHECKPOINT operation is triggered, it will write nothing in the log_reuse_wait_desc column and the transaction log file will become shrinkable.
You can find the details of the CHECKPOINT operation in the article named “Database Checkpoint”
There could be another reason such as ACTIVE_TRANSACTION. You should read the article “Transaction Log For Database is Full Due To a Reason (ACTIVE_TRANSACTION, LOG_BACKUP, REPLICATION)”
Is it possible to truncate log with TSQL?
If log_reuse_wait_desc is nothing and you shrink transaction log file with below tsql, it will truncate transaction log automatically.
|
1 2 3 |
BACKUP LOG [MyDB] TO DISK = N'C:\Backup\MyTransactionLogBackup.trn' WITH NOFORMAT, NOINIT, NAME = N'MyDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1 GO |
But be careful when backing up transaction log, it will broke your backup chain. So if you have a transaction log backup schedule and if you backup your transaction log with tsql outside of scheduled log backup with the above script, you should not lose this log backup file. Because when you want to restore your database from your backups it will need all transaction log backups. See below articles;
Point-in-time Restore With Database Recovery Advisor
Conclusion
If everything I mentioned not working and sql server transaction log not shrinking after backup and log_reuse_wait_desc nothing, backup your transaction log again and see what is going on.
Also I suggest to read the article What is SQL Server Transaction Log to understand the importance of SQL Server transaction log file.
