November 9, 2009 at 5:46 pm
I have 2 databases on a server that are giving me fits.
They have relatively small datafiles and huge transaction logs. Both were set up using bulk logged recover mode and they are being replicated nightly using snapshot replication.
One is close to 100gb the second database has a transaction log over 300gb.
I have tried to shrink the logs but cannot. I have done full backups, t-log backups, changed the recovery mode, issued checkpoints etc ad nauseum.
I have been reading and found that replication can cause issues but nobody seems to mention how to resolve them.
Bye the way, I looked at the sys.databases table and the log_reuse_wait_desc column says REPLICATION for both databases.
November 10, 2009 at 12:36 am
Hey Louis,
A wait description of 'REPLICATION' means that the log is being held active by transactional replication. The first thing to do is to check whether the database has a transactionally replicated publication. If not, you need to be sure how this has come about. Common causes include an incompletely removed publication, or the database being a restored copy of a database that does have active transactional replication.
If you find that there are definitely no transactional replication requirements, use the replication tools available in SSMS to disable transactional replication for the database concerned, and generally make sure all traces of it are gone. Depending on your configuration and the size of the snapshot publications, you may decide that it is easier to remove replication altogether and then re-configure it from a clean start.
If, having done that (or having done as much as possible) you find that the undistributed records are still causing problems, you can reset that by running the following command in the publication database:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;
This will tell the log reader process that all transactions that need to be sent to the distributor have been completed. Be careful to finish cleaning up replication after running this. While it may provide a short-term solution, unless transactional replication is fully removed, you may find the problem recurs.
Once replication is reset and configured correctly, the wait description should change to 'NONE' (or transiently to some other value). You will then be able to backup the log successfully. You may need to take two successful log backups before the physical log file can be shrunk - if you decide that is necessary.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 10, 2009 at 12:40 am
Some references for you:
Factors That Can Delay Log Truncation
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 10, 2009 at 1:21 pm
Thanks for you assistance. I had read about the sp_repldone but wasnt sure if I wanted to use that. Transactional replication is not set up on these databases and as far as I know never was.
I ended up up doin the sp_repldone and then the transaction logs shrank. Will this effect the snapshot replication?
I dont see how it should since it creates a complete snapshot of the database and then applies it to the subsribing database. I guess I will find out in the morning.
November 10, 2009 at 2:36 pm
Louis,
No it won't affect your snapshot replications.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply