Share via

Size of the Transaction Log Increasing and cannot be truncated or Shrinked due to Snapshot Replication.

  • Article

 

There can be many reasons for the size of Transaction Log growing large for a sql server database such as waiting on checkpoint, backups in Full recovery model, active long running transaction.

 

The simplest and the smartest way to determine the cause of the Transaction Log in sql server 2005 is with the use of columns log_reuse_wait, log_reuse_wait_desc in sys.databases view in Sql Server 2005 and 2008.

 

So we fire the following query to find the cause of the Transaction Log growth and observe the following

select log_reuse_wait_desc,name from sys.databases

 

log_reuse_wait_desc                                          name

------------------------------------------------------------ -------------

REPLICATION                                                                             test

 

And to our surprise the log_reuse_wait_desc column reflects REPLICATION for a particular database which has snapshot replication configured on it.

 

So we conclude the Log growth is caused due to Snapshot Replication which is kind of ironical since Snapshot Replication does not use Transaction logs to replicate data to the remote site.

 

So to confirm this we fire the DBCC OPENTRAN which gives the undistributed LSN for the Transaction log and we get an undistributed LSN value as shown below

 

DBCC OPENTRAN(13) 

 

Transaction information for database 'test’.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (43831:51:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

So this confirms that above undistributed transaction is causing the Log Growth. But in Snapshot Replication the changes are replicated using Snapshots and not using Transaction Logs.

 

We have a Known issue with Snapshot Replication in Sql Server 2005 which causes DDL changes on the tables which have SCHEMA CHANGES marked for replication where in the DDL statements are marked for Replication in Transaction log of the database but they are not unmarked when the changes are actually propagated.

 

In order to confirm whether we are hitting the issue mentioned above we need to check whether that undistributed LSN ((43831:51:1) in above case)

provided in DBCC OPENTRAN output is pointing to DDL operations of type ALTER TABLE.

 

The issue is yet to be fixed and hence the workaround to avoid the issue is to either remove the REPLICATION OF SCHEMA CHANGES for the tables involved in the replication

(Reference: https://msdn.microsoft.com/en-us/library/ms147331.aspx)

 

Or

 

Use the following commands after running Snapshot Agent and applying the Snapshot on the subscriber.

sp_repldone null, null, 0,0,1

 

Before running the above command we need to ensure that SCHEMA changes are actually being replicated in order to avoid any inconsistency

 

Note:

This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.

 

Parikshit Savjani

SE, Microsoft Sql Server

 

Reviewed By

 

Amit Banerjee
Technical Lead, Microsoft Sql Server

Comments

  • Anonymous
    June 01, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication/

  • Anonymous
    April 05, 2010
    Hi, Just wondering if MS has provided a hotfix or service pack for this yet? Thanks

  • Anonymous
    July 26, 2011
    Hello, Has there been a fix to this issue? I have a transaction replication and my log file keeps growing although my replication is working. Log_reuse_wait_desc is replication. We need our schema replicated to the target server all the time.

  • Anonymous
    February 24, 2013
    I am also having the same issue but don't know the impact that can cause teh sp_repldone so looking for some other options

  • Anonymous
    March 01, 2013
    Thanks. This helped as we ran into this issue in SQL 2008.

  • Anonymous
    January 13, 2014
    many thnanks :) i solved the problem step by step.

  • Anonymous
    February 25, 2014
    Yesterday during work i came across a situation where the transaction log file was not able to shrunk even after the log backup was taken and the log-file had grown to around 70 GB.I also confirmed that there was no open transaction in the database.After investigating further i found that the log_reuse_wait_desc for the database had the value of Availability_Replica set.I have AlwaysON setup on my databases.Please shed some light on how to proceed in this situation..

  • Anonymous
    May 13, 2014
    Thanks for sharing, this helped a lot with our issue!

  • Anonymous
    August 20, 2014
    I have no any replication setup but Sql server 2012 Evaluation version but  Sql server 2012 Evaluation version backup restored in SQL Server Standard 2012 , Then after Tran log file increase below tran open Replicated Transaction Information: Oldest distributed LSN     : (0:0:0) Oldest non-distributed LSN : (1800165:139043:1) How to resolve it ?

  • Anonymous
    October 15, 2014
    In SQL Sever 2000, Transaction Log will be increased beacause,

  1. The Recovery Model Kept as "FULL"--> Change to "SIMPLE" Note: Right Click on Database-->Select Properties-->then select "OPTIONS" Tab--> Recovery-->Model-->Simple
  2. Under "Settings" Select CheckBox "Auto Shrink" Note: Right Click on Database-->Select Properties-->then select "OPTIONS" Tab--> Settings-->AutoShrink
  3. "Automatically Grow File" should be in MBs not in Percentage. Note: Right Click on Database-->Select Properties-->then select "Transction Log" Tab--> Automatically Grow File-->in MegaBytes Ex: 1MB or 2MB preferable. After these setting click on OK button. The Database Log Transaction will remove all unused space and shrink to a specific size, from that it will grow by 1Mb or 2Mb. Can make changes to Live Database. My Database Log file grow to 136GB, When I used this method, it shrink to 36MB.

  • Anonymous
    March 13, 2015
    Thank you so much. This solved my problem. I only ran ''sp_repldone null, null, 0,0,1'' and I was able to shrink my log files to 1mb immediately after about 6 weeks of looking for solution. I watched helplessly as my database logfiles grew to 320GB.Log re-use-wait desc always set to 6 REPLICATION.The fact that i could not locate which of the subscriber servers responsible for this did not help matter. Thanks guy.

  • Anonymous
    January 28, 2016
    Thank you! It help a lot! :)

  • Anonymous
    January 28, 2016
    Thank you! It help a lot! :)

  • Anonymous
    January 28, 2016
    This post saved me from a disaster! Thanks! But this is a workaround.. Are there a definitive resolution for this problem?