August 14, 2007 at 2:28 pm
I am able to shrink the db log file manually by simply executing the script below. However, I have scheduled this script to run via sql agent which I created in a maint plan (SQL2k5 SP2) on a weekly basis and it keeps failing. The error message in the log file indicates the following:
"Could not locate file 'devdb_log' for database 'master' in sys.database_files
This makes no sense because I am specifying which db to use. For some reason Sqlagent seems to be ignoring the first command. But it works fine when executing manually.
Any suggestions?
USE [devdb]
GO
DBCC SHRINKFILE (N'DEVDB_log' , 0, TRUNCATEONLY)
GO
Thanks.
August 14, 2007 at 2:35 pm
Take out the use, and specify the db as part of the job. If you look at the error message I bet it is complaining about the USE statement. FYI: Post the complete error message.
August 14, 2007 at 2:42 pm
Here is the entire query. How do you specify the db as part of the job?
Executing the query "DBCC SHRINKFILE (N'DEVDB_log' , 0, TRUNCATEONLY)
" failed with the following error: "Could not locate file 'DEVDB_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.
August 14, 2007 at 4:53 pm
I am going to assume you are using the GUI and not doing this by hand. So, when defining the step, there is a drop down that has the database (right above the command section).
If you are doing this by hand.
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'bobtest', @step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select 1',
@database_name=N'devdb',
@flags=0
August 15, 2007 at 7:28 am
There are four parts to a fully qualified table name in SQL Server:
server, owner, database, and table.
In multiple threads on this forum, you will often see at least 3 of the 4 explicitly named in code samples. On a lark, try searching for the string '.dbo.' and see what comes up. Then apply the various name forms until you find one that works for you and you are comfortable with.
GUI is convenient, but sometimes leaves you limping (aka shot in the foot)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply