Home » SQL ServerRSS

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
Go

Backup log xxx with truncate_only
go
Dbcc shrinkfile(xxx_Log)
Go

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
Go

Backup log  xxx with truncate_only
go

Use YourDatbaaseName

Dbcc shrinkfile(xxx_Log)

Madhu

 

Answer 2

Hi.
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];
GO
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 ?

Preet

 

Answer 5

What is the Service pack on this box?

Madhu

 
 

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.

Madhu

 

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

Hi,

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

Guys,

 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

USE

 

 

[dbESR3];

GO

USE

 

 

[dbESR3];

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 .

Use

 

master

Go

Backup

 

log adventureworks withtruncate_only

go

Dbcc

 

shrinkfile(AdventureWorks_Log)

Go

 

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.

HTH

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter