Search This Blog

Cannot shrink log file 2 because the logical log file located at the end of the file is in use.

As a dba, several guys asked me this question on many occasions. All they want to do is free up some space from the transaction log file , and while running the DBCC SHRINKFILE() the above error shows up.  As you can see from the error, it does not shrink because the file is in use; So you have two options, either change the recovery to simple, shrink the file then change the recovery back to full. But this may not be good idea, if you are log shipping, in that case,  need to make sure that there are no open transactions, and then shrink the file

dbcc opentran ([YourDatabase])
GO
CHECKPOINT
GO
USE [YourDatabase]
GO
DBCC SHRINKFILE (N'YourDatabase_log' , 0) – I prefer ‘0’ because it frees up all the available space
GO

1 comment:

lee woo said...

Panic causes tunnel vision. Calm acceptance of danger allows us to more easily assess the situation and see the options. See the link below for more info.


#options
www.ufgop.org