SQL Job appear to run when job has been disabled???

  • We've apparently had the same problem. In disabling jobs (jobs that restore backups down to our Dev server) during month end, the job gets disabled but then still runs as scheduled anyway. The decision was made that we had to disable both the job and the schedule to truly disable the job.

    But are you saying that it's a cache issue?

    EDIT: Nevermind. Re-read the thread and think I understand it now. That makes sense.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I believe it is.

    I was directly updating the field enabled in the table msdb.dbo.sysjobs, setting it to 1 or 0, depending on whether I wanted to enable it or not.

    Although the T-SQL updated the record it the job still run, as previously described.

    To resolve the issue, I changed my process to use the stored procedure sp_update_job

    for example:

    exec sp_update_job @job_name = 'MyJobToDisable', @enabled = '1'

    Jobs then enabled / disabled as expected.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • I'm going to have to do more research. I'm now being told this happens when disabling jobs through the GUI, not just the code.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie

    This definitely happens when disabling from the GUI (see previous post). The system SP runs another SP, sp_sqlagent_notify, which clears the cache for the job; again, see previous post. So you can either use the SP or duplicate what it does, but in either case it's a T-SQL code task, not something the GUI does well - and generally direct updates to system tables are bad, Dave-DJ, so the sp_update_job definitely wins out. My other fix was to disable the schedule - I guess if it's a shared schedule then that would stop any jobs using that schedule from running (although I've not tested that - I don't generally use shared schedules).

  • simon-811452 (4/9/2010)


    Brandie

    This definitely happens when disabling from the GUI (see previous post). The system SP runs another SP, sp_sqlagent_notify, which clears the cache for the job; again, see previous post. So you can either use the SP or duplicate what it does, but in either case it's a T-SQL code task, not something the GUI does well - and generally direct updates to system tables are bad, Dave-DJ, so the sp_update_job definitely wins out. My other fix was to disable the schedule - I guess if it's a shared schedule then that would stop any jobs using that schedule from running (although I've not tested that - I don't generally use shared schedules).

    This is what i am exactly going through. I have disabled bunch of jobs through GUI (like selected 5 jobs together and right clicked, disabled) and renabled the same way. After re-enabling the jobs didn't run as per the schedule. However the jobs did run after i disabled-re-enabled the jobs "through script". Can you please say what is the best way to avoid this kind of situation? Thanks

  • What service pack are you on ? Sometimes these odd problems occur in early versions, and are fixed in later SPs.

  • homebrew01 (2/15/2011)


    What service pack are you on ? Sometimes these odd problems occur in early versions, and are fixed in later SPs.

    sql 2005 stnd edition 64 bit with SP3 + CU10.

  • sp_update_job is definitely the way - the GUI disable and updating the table directly both seem to cause failures.

  • simon-811452 (2/15/2011)


    sp_update_job is definitely the way - the GUI disable and updating the table directly both seem to cause failures.

    OK if i follow this correctly you mean to say the GUI is running the mentioned below:

    i) EXEC msdb.dbo.sp_update_job @job_id=N'5bd63372-c981-4cef-b18f-2db4b464ee43',

    @enabled=0

    ii)UPDATE msdb.dbo.sysjobs

    SET name = @new_name,

    enabled = @enabled,

    description = @description,

    start_step_id = @start_step_id,

    category_id = @category_id, -- Returned from sp_verify_job

    owner_sid = @owner_sid,

    notify_level_eventlog = @notify_level_eventlog,

    notify_level_email = @notify_level_email,

    notify_level_netsend = @notify_level_netsend,

    notify_level_page = @notify_level_page,

    notify_email_operator_id = @notify_email_operator_id, -- Returned from sp_verify_job

    notify_netsend_operator_id = @notify_netsend_operator_id, -- Returned from sp_verify_job

    notify_page_operator_id = @notify_page_operator_id, -- Returned from sp_verify_job

    delete_level = @delete_level,

    version_number = version_number + 1, -- Update the job's version

    date_modified = GETDATE() -- Update the job's last-modified information

    WHERE (job_id = @job_id) --- And this is causing the issue?

  • update..from the previous post which i mentioned step ii is also part of step i. My question is same now, what is the difference between GUI and running through script.I just see this one proc " sp_get_composite_job_info" not part of sp_update_job.

  • The problem is, we don't know what the problem is. Both GUI and script should work. GUI is not working properly, however. So stick with the script or, as I do, do it through the GUI and run the update sproc afterward.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • sqldba_icon (2/15/2011)


    simon-811452 (4/9/2010)


    Brandie

    This definitely happens when disabling from the GUI (see previous post). The system SP runs another SP, sp_sqlagent_notify, which clears the cache for the job; again, see previous post. So you can either use the SP or duplicate what it does, but in either case it's a T-SQL code task, not something the GUI does well - and generally direct updates to system tables are bad, Dave-DJ, so the sp_update_job definitely wins out. My other fix was to disable the schedule - I guess if it's a shared schedule then that would stop any jobs using that schedule from running (although I've not tested that - I don't generally use shared schedules).

    This is what i am exactly going through. I have disabled bunch of jobs through GUI (like selected 5 jobs together and right clicked, disabled) and renabled the same way. After re-enabling the jobs didn't run as per the schedule. However the jobs did run after i disabled-re-enabled the jobs "through script". Can you please say what is the best way to avoid this kind of situation? Thanks

    I just realized this was an old thread. Sqldba_Icon, given that you have your own thread on this problem, could you please post all your questions and responses there, not here? You're just going to confuse people with duplicate posting.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks and will do so.

  • I did a quick test as following:

    1) Created a test job with a simple t-sql step and scheduled the job to run every minute.

    Test:

    1) Enabled Job - Enabled Schedule - Job runs at schedule

    2) Disabled Job - Enabled Schedule - Job DOES NOT run

    Now, I tried to mess around with it by:

    3) Enabled Job - Modified Schedule

    I had the job monitor refresh every 3 seconds

    In the agent, the snapped time for the next run for the job remains the same as per the old schedule even though the schedule was modified to run at a later time or earlier time.

    After the job runs at the last snapped schedule time in the next run for the job (as per the old schedule), the next run snap time modifies as per the new schedule but not at the time when the change in schedule is made.

    I guess the job schedule in the cache is refreshed for each job only after the last cached next run time for the job is past.

    May sound silly but something i wanted to share with you guys.

    Thanks

    -

    John

  • I did a quick test as following:

    1) Created a test job with a simple t-sql step and scheduled the job to run every minute.

    Test:

    1) Enabled Job - Enabled Schedule - Job runs at schedule

    2) Disabled Job - Enabled Schedule - Job DOES NOT run

    Now, I tried to mess around with it by:

    3) Enabled Job - Modified Schedule

    I had the job monitor refresh every 3 seconds

    In the agent, the snapped time for the next run for the job remains the same as per the old schedule even though the schedule was modified to run at a later time or earlier time.

    After the job runs at the last snapped schedule time in the next run for the job (as per the old schedule), the next run snap time modifies as per the new schedule but not at the time when the change in schedule is made.

    I guess the job schedule in the cache is refreshed for each job only after the last cached next run time for the job is past.

    May sound silly but something i wanted to share with you guys.

    Thanks

    -

    John

Viewing 15 posts - 16 through 30 (of 32 total)

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