Job won't stay Enabled
-
My job stays enabled all day but the next morning it shows disabled for no particular reason. I am the only one in this SQL Server. Why is it not staying enabled?
-
Here is the exact script. It is on SQL2000 Standard Edition. Why won't it stay enabled?
-- Script generated on 9/9/2003 11:11 AM
-- By: sa
-- Server: GP1
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'COL backup') > 0
PRINT N'The job "COL backup" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'COL backup', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'BACKUP DATABASE [COL] TO [COL_backup] WITH INIT , NOUNLOAD , NAME = N''COL backup'', NOSKIP , STATS = 10, NOFORMAT ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'COLschedule', @enabled = 1, @freq_type = 8, @active_start_date = 20030710, @active_start_time = 60000, @freq_interval = 62, @freq_subday_type = 4, @freq_subday_interval = 15, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 20031231, @active_end_time = 190000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-
Have you tried leaving profiler running over night to see if any sql is being fired at the server?
Steven
-
Any chance the date is wrong on the machine? You've an end date set for the schedule, once it passes that the job gets disabled.
Andy
-
I tend to agree with Andy. Maybe the time on the server is wrong, OR it temporarily gets set wrongly overnight, causing SQL Agent to disable your job.
Maybe check the SQL Errorlog (using a text editor, not EM) to see if the times/dates stamped therein change at all.
Is this the only job you have that has an end date set?
If you run out of ideas you can maybe create an update trigger on sysjobs, looking for changes to the enabled column.
Cheers,
- Mark
Edited by - mccork on 09/09/2003 3:11:50 PM
Cheers,
- Mark -
It happened some day with one of my job too. It was the only problem job on the server, all others stayed enabled. I recreated it from scratch and it resolved the issue. No theory...
-
I'm put an update trigger on sysjobschedule.enabled column. IT seems this is the filed that keeps getting changed from a 1 to a 0 at night. The servers date is correct and 'yes' I am aware that the end date for this scheduled job is December 31, 2003. Somehow the sysjobschedule.enabled field is getting modified. I'll have more info tomorrow based on my trigger spy. Thanks...
-
trigger didn't tell me much other than it disabled after the last run of the night. I next tried to re-create it. That didn't work so finally just added another step to always set the field to enabled using the following SQL: update sysjobschedules set enabled = 1 where schedule_id = 7. Your schedule_id will be different of course. Strange bug I still don't know why it sets this field in the sysjobschedules table in the msdb dB to zero each night. Oh well, this works.
-
Does this help?
http://support.microsoft.com/default.aspx?scid=kb;en-us;295378&Product=sql2k
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply