As part of your DBA duties, you’re probably restoring your production databases into a test server. Then one day you notice that your log volume is getting pretty close to full! You take a look in the transaction log folder, and notice that that log file is even bigger than the data file. You did remember to set the recovery mode to simple, so why is the log file so big?
Naturally, you run DBCC SHRINKFILE against the log file, but the file doesn’t shrink. You take a peek at sys.databases, and the LOG_REUSE_WAIT_DESC column says “REPLICATION”.
“It’s waiting for replication? Odd, I haven’t set replication on this test server!
A bit of background
Internally, a database log file is divided into smaller blocks called Virtual log files (VLF). During the normal course of events, a VLF is filled with transactions, and certain flags control when a VLF can be overwritten. The whole topic is super complex, so for much more detailed explanation, read here.
When a database is setup as a replication publisher, one of the VLF “don’t overwrite me, bro!” flags is held in place until the replication log reader has had a chance to read the contents of the VLF and copy them into the Distribution database (again, this is over-simplifying things).
In this issue, the database was backed up while those flags were still in place, so the flags were restored in your test server! Since my test server isn’t replicated, there’s nothing actively reading the VLF and clearing the flags, so the VLFs are never allowed to be reused. This is why the log_reuse flag is set to “Replication”, and the result is that the log will just grow forever (or until you run out of space, whichever comes first).
The easiest fix is to run the command below — It’ll remove all the replication objects, flags, etc, from the database. After that, your shrink should work as expected:
USE master
EXEC sys.sp_removedbreplication @dbname = 'your database name', @type ='both'