3

Our database log (SQL Server 2008 R2) is growing too big, and truncating it via DBCC SHRINKFILE doesn't work (the database is set to simple recovery mode).

A bit of background. I released some changes to our live servers a few days ago, I needed to drop some articles from the snapshot replication which I did, made the changes, and re-added the removed articles. However, when I tried to reinitialise the subscribers I got a strange error (which I didn't save unfortunately and am not allowed to recreate until the next release window). I set the replication processes running again but it seems to be holding a lock on the log file.

 select log_reuse_wait_desc from sys.databases where name = '<dbname>'

gives a result of 'REPLICATION'.

DBCC OPENTRAN ('<dbname>')

gives

Transaction information for database '< dbname >'.

Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (3891297:327:1) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've done some reading around and I suspect that the way to fix it is to kill off a blocking spid or use sp_repldone somehow, but...

exec distribution.dbo.sp_browsereplcmds @xact_seqno_start = '0x003B6061000001470001', @xact_seqno_end = '0x003B6061000001470001'

returns nothing. I'm not sure that I've converted the lsn correctly.

I'm not the DBA, he is unavoidably out of the country at the moment and getting him working on this would be quite bad. I'd prefer to fix the log growth problem and have him look into why reinitialisation doesn't work when he gets back, but this is a time sensitive problem since we will run out of space sometime on Sunday at the current rate of growth.

I can't do anything too drastic since we have other publications (transactional replication) running on the same server. Any ideas on what I should do would be very appreciated right now.

2

If you are sure that everything is replicated to all subscribers and you really need to shrink it right now, you can try this.

  1. Execute the following snippet inside your replication database. It will mark all replications as successful and your replication queue will be empty. Be sure that this is really the case! Otherwise you may loose some elements which need to be replicated.

Script:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, 
     @numtrans = 0, @time = 0, @reset = 1
  1. Take a backup and tail the log. It should now be empty.
  • is there any way of targetting this at just the database that is causing the problem? the other replications on the same server are transactional and constantly updated. – MLT Jul 3 '15 at 11:19
  • Yes, as mentioned. Run it in the database you need to shrink. It will work only per database. – Ionic Jul 3 '15 at 11:21
  • Due to the fact you marked this answer as your question. I think it worked for you just on those mentioned database. :-) – Ionic Jul 3 '15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.