14 Answers Found

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];GODBCC 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.

Hi Preet 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

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.

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...).

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.