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] GO ALTER DATABASE mydatabase SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE mydatabase SET RECOVERY FULL WITH NO_WAIT GO
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.