Wow, it has been a while since I’ve posted. It isn’t that I’ve been lazy but I’ve been busy doing things. I ran into this one that took me a bit of time to fix and wanted to post it for future reference.
Cannot shrink log file 2 () because all logical log files are in use
Today while running a script in a dev environment, the log file grew to 135GB leaving only 9mb left on the log drive. When I tried to free the space back to the OS, it wouldn’t do anything. I tried to change the recovery mode, from SIMPLE to FULL, FULL to SIMPLE, SIMPLE to BULK and BULK to SIMPLE. In between each change, I would attempt to release the space back to the OS. But it wouldn’t budge.
Each time when I attempted to shrink, I would get the message:
Cannot shrink log file 2 () because all logical log files are in use.
I haven’t seen this message before so addition effort was exerted such as:
- taking the database offline and online again
- running dbcc checkdb
- full backup of the database
I don’t know why I didn’t think of it earlier but I wanted to see what the log is stating, so I ran the following:
SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = ‘db_name’
The result said: REPLICATION
I was like WTF! REPLICATION?! I looked at replication monitor and did not see any replication. It seem that this damn database was restored from an environment where replication was setup. So I ran:
EXEC sp_removedbreplication ‘db_name’
It ended with an error but when I looked at the log_reuse_wait_desc again, it said: NOTHING. Once I saw this, I went ahead and release the space back to the OS and BAM, 135GB of space was recovered.