July 14, 2014 at 10:13 am
Hi All,
I have a production database which uses merge and snapshot replication. The Merge is for 3 tables. The snapshot is to update the rest of the data once daily. I use a Full recovery model and perform database backups (full, differential) and transaction log backups.
I have a database optimization plan which runs 4 times a week. This plan performs and integrity check and rebuilds the indexes. This optimization plan is growing the transaction log by about 8MB each time it is run and we are running out of space on the drive for our log files. The space is not being reused.
I saw in another post where Gail Shaw suggesting using SELECT name, log_reuse_wait_desc FROM master.sys.databases to see why the log space is not being reused. On the database in question, the above returns "REPLICATION".
A colleague tried to backup the transaction log a couple of times to truncate the log this weekend. She was going to perform a DBCC Shrinkfile command afterwards. But the truncate failed. Again looking into things it seems replication prevented the truncation.
We are looking at stopping the merge replication or even removing it to truncate the log file and then recreate the merge replication. I'm looking for suggestions on how to handle shrinking the log file for now and then seeing if there are any checks or changes I can perform which will allow the transaction log space to be reused.
July 14, 2014 at 10:27 am
Merge doesn't use the transaction log. Are you sure there's no transactional replication set up, intentionally or otherwise? Transactional replication is what can prevent log reuse.
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
July 14, 2014 at 10:29 am
Also, find my "Why is my transaction log full" article here. There's a link in there to a kb article on a bug in snapshot replication (I think) which can cause this.
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
July 14, 2014 at 12:28 pm
Thanks for the response. Though I learned from the article, it did not help me resolve me issue. I've double checked and we are only using merge and snapshot replication. So, I'm still stuck as to why I can't get the log file to reuse space or to truncate upon backup.
July 14, 2014 at 2:10 pm
Not the article itself. The kb that it links to which describes a bug with (I think) snapshot replication.
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
July 14, 2014 at 3:44 pm
How does Subscriber synchronizes with the Publisher (Merge replication) if it is not using trn log?
July 14, 2014 at 3:50 pm
Barcelona10 (7/14/2014)
How does Subscriber synchronizes with the Publisher (Merge replication) if it is not using trn log?
It's thoroughly documented in Books Online.
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
July 14, 2014 at 4:04 pm
Thank You.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply