Unable to Shrink the Log of a Replicated Database

Okay, so I realize that it’s not a good thing ever to shrink any database file. However there are times where it simply makes sense to not allocate resources so much to a process that achieves very little.

In my particular example, I have a database which rarely has transactions. It’s a 9GB database and to my surprise, the transaction log was 25GB. This database is in simple recovery mode and gets backed up daily. There’s no real reason why the log should have been so large.

First I tried the traditional approach to shrinking. I executed dbcc shrinkfile(2). No results. I tried backup log <DatabaseName> with truncate_only. No results. I tried checkpoint, then backup log, then shrinkfile. No results. I changed the recovery mode and changed it back. Still, nothing would free up this space.

I executed dbcc loginfo, and it told me that 100% of the log was used. There were no open transactions.

Finally, the last thing I tried which cleared everything up was the following commands:

exec sp_dboption ‘DatabaseName’,’Published’,’true’

exec sp_repldone @xactid=NULL,@xact_segno=NULL,@numtrans=0,@time=0,@reset=1

exec sp_dboption ‘DatabaseName’,’Published’,’false’

dbcc shrinkfile(2)

exec sp_dboption ‘DatabaseName’,’Published’,’true’

 

Please note to be careful when doing this. In this particular case, this database is using Snapshot replication. Using Transactional or Merge replication, you may run into some issues. Review sp_repldone before executing this in a production environment.

Advertisements