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


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




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





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



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 (14)

  1. Victor says:


    Just wondering if MS has provided a hotfix or service pack for this yet?


  2. ASR1 says:


    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.

  3. biju Jose says:

    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

  4. Darin says:

    Thanks. This helped as we ran into this issue in SQL 2008.

  5. yavuzcel says:

    many thnanks 🙂 i solved the problem step by step.

  6. Vineeth Panicker says:

    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..

  7. JMLO says:

    Thanks for sharing, this helped a lot with our issue!

  8. vipul says:

    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 ?

  9. says:

    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.

  10. Ademola Ola says:

    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.

  11. FLauffer says:

    Thank you! It help a lot! 🙂

  12. FLauffer says:

    Thank you! It help a lot! 🙂

  13. FLauffer says:

    This post saved me from a disaster! Thanks!

    But this is a workaround.. Are there a definitive resolution for this problem?

Skip to main content