Problems Shrinking SQL Server Log Files

When the database log files of are clogging my harddisks again, I shrink them using a simple script. (Yes, I know you usually don’t need to do this, but on a dev machine harddisk space can become valuable occasionally)

So I started the script and received the error message

could not locate file ‘mydatabase_log’ for database ‘mydatabase’ in sys.database_files. The file either does not exist or was dropped.

Since the log file name is retrieved from the sysfiles view, this could not be a typo. I also checked the sys.database_files, but the log file name displayed is the same name as in sysfiles. Shrinking from the Tasks/Shrink menu resulted in the same error message.

Fortunately, I found the solution on MSDN Social

The solution was to change recovery mode for the database to something else and then back again. E.g. if recovery mode is “simple” switch to “full” then back to “simple”. Or vice versa.

Changing the recovery mode can either be done via the Database Properties dialog, or in TSQL using

USE [master]

This procedure worked for the databases that showed this behavior.

For another couple of databases, DBCC SHRINKFILE raised the error message

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

Whatever the message means, it causes SHRINKFILE to not shrink the file.

Again, the solution was on MSDN Social

The solution is change the recovery mode to SIMPLE, shrink log files and then change to the FULL recovery mode.

PS: It’s really annoying when searching for solutions using the “correct” search terms, you inevitably come across “answers” such as

you didn’t get the right name of the log file in the DBCC command.

Definitely you have given wrong file name.

Please read the questions to figure out whether the questions are “educated” or “newbie” before posting such comments. Really.

1 thought on “Problems Shrinking SQL Server Log Files

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.