Workaround for Delay in Transaction Log Truncation: Snapshot Replication
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
- SSMS: Using Registered Servers to Manage Servers
- Release Notes
- Privacy Statement
- Announcing Build 2014
- SQL Server Data Tools Team Blog
- SQL Server 2014 Portal