Sql-server – Unable to shrink log file due to snapshot replication

replicationsql-server-2008-r2transaction-log

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.

Best Answer

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.
Related Question