January 23, 2012 at 11:58 am
Hello,
I have a SQL 2008 environment
I am running a snapshot replication at 9:30 PM everyday to our read only reporting server.
I am having a problem with the log file growing and not releasing data. when i checked the log_reuse_wait_desc it was set to replication.
My current Log file is 30 GB and my DB is 7 Gb I run transactional backups every 30 minutes and a full backup nightly. I have over 2000 VLF when i check with my Monitoring program..
How can I get the LDF to release the data then my snapshots only occur once per day or change the log_reuse_wait_desc to log_backup
thank you
Thomas
January 23, 2012 at 12:02 pm
Have a read through the replication section of the following article, it's got a link to a known problem with snapshot replication that causes this
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2012 at 1:17 pm
Gail,
I have ran those queries and the results come up blank I have read the article prior to my post and had already tried that query
January 23, 2012 at 1:21 pm
Which queries?
Did you read over the blog post on snapshot replication that the article references?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2012 at 1:53 pm
Thanks Gail I will try to see if that works
January 23, 2012 at 2:09 pm
Gail,
I ran the following query on open
DBCC OPENTRAN
Trans Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (24088:9523:1)
So according to the article after my replication completed i have ta add a task to reset the replication indicator ?
sp_repldone null, null, 0,0,1
Is this correct ?
January 23, 2012 at 2:15 pm
Quote from the blog post in question (http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx)
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: http://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
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply