Thursday , March 28 2024

SQL Server Does Not Truncate the Transaction Log After Log Backup and Can Not Shrink the Transaction Log

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.

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.

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.

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;

Backup Restore On SQL Server,

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.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

3 comments

  1. Transaction log file full due to replication and this instance configured with replication
    how to clear the log space?

  2. Pugazh Veerasamy

    Nice explanation, it worked for me. Was struggling to shrink the log files for 2 days, this helped me.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories