Home » SQL Server

Could not locate file 'xxx_Log' for database 'master' in sys.database_files

Hi All,

I am  truncating Transactionlog…   following the steps mentioned in MSKB.. what is the problem?

Use master

Backup log xxx with truncate_only
Dbcc shrinkfile(xxx_Log)

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'xxx_Log' for database 'master' in sys.database_files.
The file either does not exist, or was dropped.

Please  help me.

Thanks in advance


14 Answers Found


Answer 1

SHrinkfile has to be run from the particular database  to be shrinked

 Follow these steps

Use master

Backup log  xxx with truncate_only

Use YourDatbaaseName

Dbcc shrinkfile(xxx_Log)



Answer 2

I am executing exactly as you specified (changing database  context with USE) in a "Maintenance Plan" using an "Execute TSQL Statement Task". This plan is then scheduled to run.
It fails with "...Could not locate  file 'XXX_Log' for database 'master' in sys.database_files. The file  either does not exist, or was dropped."
It seems that it is unable to execute the "USE myDatabase" command correctly.

Here is the TSQL in full ;

USE [myDatabase];
DBCC ShrinkFile('XXX_Log', 3000)

This Maintenance Plan still fails even if I execute it manually (i.e. not in a job).
When I remove the ShrinkFile statement it succeeds i.e. the USE statement seems to be executing.

Any ideas ?


Answer 3

Hi Preet Smile
             Could you pls check your logical file  name of your log  file once again. Execute sp_helpdb 'your db name' from that you can get the logical file name something like 'xxx_log'. May be that might be one of the reason....
- Deepak

Answer 4

Hi Deepak

Logical name is definitely correct (and as returned by sp_helpdb) and the command works in a normal query window.

Can you get it to work in a Maintenance Plan, Execute T-SQL Statement Task ?



Answer 5

What is the Service pack on this box?



Answer 7

To troubleshoot , can you open this maintenance plan in BIDS and see the connection.

(a) Check which database  is it connecting, if its not your database change the connection to your db and see

(b) Check which user is it using and what is the default database of the user. Change the default database to your db and check.



Answer 8

Opened in BIDS. The connection does not specify a database. When I specify the database  it still fails. No specfic error the Progress tab just indicates that the TSQL failed.
I could try changing the default database as you suggest in b) but this will not help if I want to shrink several databases/files.

I decided to put the TSQL commands in a SQL Job. This works fine.

I would be interested to know if anybody has got a similar "Execute TSQL Statement" in a Maintenance plan to work.

Thanks for your help.


Answer 9


I tried to use a similar command to shrink the log  files in a maintenance plan and got the same error after the scheduled execution. It seems like before the DBCC shrinkfile command it switches back to use the master  database. After adding another "USE mydb" just before the dbcc shrinkfile command (without GO between the lines) the plan ran successfully. Try and see if it works for you (if you still need it...).


Answer 10

I dont need it as I have put it in a SQL Job Step but thanks for the tip.

If I use it and I remember, I'll let you know if it worked,


Answer 11

Thanks! This tip works well for me.


Answer 12


 I am having the same problem  and unable to get it to work. I am not setting up a maintenance plane but running as a query for now. i have verified that the logical name is correct. Please hlp










DBCC ShrinkFile (dbESR3_Log, 500)



Msg 8985, Level 16, State 1, Line 2

Could not locate  file 'dbESR3_Log' for database  'dbESR3' in sys.database_files. The file  either does not exist, or was dropped.


Answer 13

Please run sp_helpfile in that database  and verify that you are using the correct file  name while shrinking

Answer 14


Anyone can reproduce it .







log adventureworks withtruncate_only







Reason : you are doing USE master  and doing DBCC shrinkfile for some other database  in master .So none of the logical files assocoated with master will have the AdventureWorks_Log as its logical file  name .So we are getting this error .

So as per BOL :DBCC SHRINKFILE applies to the files in the current database. Switch context to the database to issue a DBCC SHRINKFILE statement referencing a file in that particular database.

therefore you need to use "USE DB" before running it for xxx_Log .Backup log  with truncate will work fine without this need.




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure