Post reply

Job won't stay Enabled

  • September 9, 2003 at 10:56 am

    #358245

    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?

  • September 9, 2003 at 12:19 pm

    #473287

    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:

  • September 9, 2003 at 12:22 pm

    #473288

    Have you tried leaving profiler running over night to see if any sql is being fired at the server?

    Steven

  • September 9, 2003 at 2:31 pm

    #473311

    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

    http://qa.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • September 9, 2003 at 3:07 pm

    #473318

    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

  • September 10, 2003 at 9:33 am

    #473464

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

  • September 10, 2003 at 10:27 am

    #473495

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

  • September 19, 2003 at 9:53 am

    #475078

    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.

  • September 21, 2003 at 8:17 pm

    #475188

    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