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>')
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.