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
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.
SE, Microsoft Sql Server
Amit BanerjeeTechnical Lead, Microsoft Sql Server
PingBack from http://asp-net-hosting.simplynetdev.com/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication/
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
Thanks. This helped as we ran into this issue in SQL 2008.
many thnanks :) i solved the problem step by step.
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..
Thanks for sharing, this helped a lot with our issue!
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
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (1800165:139043:1)
How to resolve it ?
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.