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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s