Using DBCC SHRINKFILE to shrink the transaction log Errors with 'Cannot shrink log file 2 (LogFileName) because all logical log files are in use.'

I recently had a (relatively small, 4gb) database blow out with a transaction file of 160gb+. No matter what I tried I couldn’t truncate the logs and as a result I couldn’t shrink the file. Whenever I tried to run a DBCC SHRINKFILE to shrink the transaction log. SQL would error after a short time with ‘Cannot shrink log file 2 (LogFileName) because all logical log files are in use.’

I recently solved this issue and it was a doozy; I found that in sys.databases, log_reuse_wait_desc was equal to ‘replication’. Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space.

Continue reading…