Execute T-SQL Statement Task issue

Question

Execute T-SQL Statement Task issue

Feeg

SSCrazy

Points: 2766
More actions
July 13, 2010 at 5:28 am

#141035

USE [UserDB];
GO
DBCC SHRINKFILE ('UserDB_Log', 3072); --3 GIG Shrink of Log File
GO
This is the statement but I get an error 'xecuting the query "DBCC SHRINKFILE ('UserDB_Log', 3072) --3 GIG Shrink of Log File
" failed with the following error: "Could not locate file 'UserDB_Log' for database 'master' in sys.database_files. The file either does not exist, or was dropped. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.'
Why is it complaining about not finding the file in the master db when I have USE [UserDB]???
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

magasvs SSCertifiable Points: 7660 More actions · Answer 1

Could you check the database status and run CheckDB? One of the reasons you can get this error is when the database is offline.

EXEC sp_helpdb 'UserDB'

GO

DBCC CHECKDB('UserDB')

GO

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 2

I knew its a silly Q.. Can u pls check whether its a correct filename u have mentioned ( as per ur database).?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Hi

Try to use UserDB_Log instaed of using 'UserDB_Log'.

Thank You,

Best Regards,

SQLBuddy

Feeg SSCrazy Points: 2766 More actions · Answer 4

hi sql peeps, when I run the query I posted above in a new query window in the management studio it runs without any issues. If I run it in the master db without the [USE Userdb] statement I get the exact error message as when I insert my complete statement in the T-SQL Statement Task within a maintenance plan.

It is as if the [USE Userdb] is not used in the T-SQL Statement Task ...

Anybody else get this issue???

That is the correct filename and the db is not offline as I tested the statemet in the management studio new query window before I implememnted it in the T-SQL Statement ...

Is this a MS SQL 2005 error??? SP2 is installed!!!

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

OomBoom Hall of Fame Points: 3481 More actions · Answer 5

make sure that the account that it runs under in maint has access to the userdb. Try removing GO between use userdb and dbcc commands

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>