Workaround for Delay in Transaction Log Truncation: Snapshot Replication

  • Article

https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.pngGold Award Winner

Overview

The following article was previously created as a blog post on http://felipelauffer.wordpress.com/2016/02/24/ and some parts were modified to best view in Wiki. 

Sometimes we may encounter scenarios where the transaction log of a database configured with Recovery Model FULL continues to grow and it is never truncated even if the transaction log backups occur frequently. This behavior can occur for several reasons: active transactions, backups/restores in progress, replication, among other reasons, which you can check out in this link: https://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx

In this article we will talk about a specific reason delaying truncation of the transaction log: REPLICATION. More specifically SNAPSHOT REPLICATION and how to apply a workaround solution for emergency situations.

All the commands and queries in this article you can find at the TechNet gallery in the link below:
https://gallery.technet.microsoft.com/Workaround-for-Delay-in-564d033a

Diagnostic

To check the reason for not reusing the transaction log we can always use the log_reuse_wait_desc column in system table sys.databases as the following query:

Like we can check in the image below the reason for log not be truncated/reused is due to a configured replication in the SQL Server instance.

If this configured replication is a SNAPSHOT REPLICATION, your transaction log is FULL, you are out of disk space and do not know what to do, start by running the following command in your database:

If the result of the DBCC OPENTRAN command is similar to the return shown above there is a workaround.

Workaround

In the same database that is with full transaction log and it can not be reused due to replication, as viewed earlier in log_reuse_wait_desc column, execute the following command:

After running this command, replication dependencies must be resolved and your transaction log can be reused. Now you must ensure that replication is replicating all schema changes to ensure the integrity of it.

Warning: The sp_repldone command should be used only in emergency situations. This should not be regarded as the definitive solution to the problem, but only a workaround.

See Also