Job stops running as per schedule after maitenance?

  • We disabled set of jobs before maintenance and enabled them back after maitenance .Since then the jobs are not running as per the schedule. Mentioned below are my findings:

    i) job runs fine if i run it manually.

    ii) job schedule runs fine if i enable/disable the job.

    iii) ONLY the jobs which i disabled are having this problem, all the others ones are running fine.

    iv) our sql version is sql 2005 std edition 64 bit with SP3+CU10.

    Any idea?

  • Check the schedule on the job. It is probably disabled as well. Instead of disabling the job, if you disable/renable the schedule on the job I think you'l run into fewer problems.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (2/14/2011)


    Check the schedule on the job. It is probably disabled as well. Instead of disabling the job, if you disable/renable the schedule on the job I think you'l run into fewer problems.

    I am looking at it right now as i am typing, the schedules are enabled. If i disable/enable job or the schedule ONLY FROM A SCRIPT it works, not from the GUI, only when i do from the script it works.

  • Drop the job and prepare a new one

  • J,K (2/14/2011)


    Drop the job and prepare a new one

    Thanks. I am not looking for a solution here, as i mentioned in my post "ONLY FROM A SCRIPT" works. I am pretty aware that dropping and creating will work ( i am talking about more than 300 jobs), what i am trying to find is what possibly could have gone wrong that "after disabling and enabling a job from GUI" messed up the schedules , i mean the jobs didn't run as per the schedule and i am 101% confident schedule and job was enabled but just didn't kickoff as per the schedule.Thanks

  • You might want to check out the metadata behind the job in sysjobs and sysjobschedules and see what happens when you toggle them using the GUI vs the script. Definitely odd behavior.

    Are you running the latest tools, i.e. SQL Server 2008 R2 and patched or are you running 2005 SSMS? If you don't see any metadata differences between changing them via the GUI vs the script then it might be worth a call to MS. Of course if you are behind in versions then I would suggest getting that upgraded as well.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Could it be that the job cache hasn't been updated since right after you disabled things?

    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 Tarvin (2/15/2011)


    Could it be that the job cache hasn't been updated since right after you disabled things?

    Could be..what do u mean by "job cache"..how can i check that?

  • sqldba_icon (2/15/2011)


    Brandie Tarvin (2/15/2011)


    Could it be that the job cache hasn't been updated since right after you disabled things?

    Could be..what do u mean by "job cache"..how can i check that?

    Job cache is a memory cache where SQL Server stores the "latest" versions of jobs, including enabled or disabled status. I'm not sure where you can check it, but you can test it.

    Pick a job_id of one of the jobs that's not running correctly and replace the job_id in the code below. Run the proc, which updates the job cache, then see if the job runs during its next scheduled time. If it does, you know it's a job cache issue.

    exec msdb.dbo.sp_sqlagent_notify @op_type = N'J', @job_id = '0D5F4FE4-81EB-4299-8AE9-CCB803FEAB01', @action_type = N'U'

    GO

    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.

  • Of course, according to BOL it is supposed to update the cache with every change but it may not be happening here. I haven't heard of the job cache having issues in the past but what Brandie is suggesting is certainly logical, albeit somewhat scary.

    I'll be interested to hear what is found here.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (2/15/2011)


    Of course, according to BOL it is supposed to update the cache with every change but it may not be happening here. I haven't heard of the job cache having issues in the past but what Brandie is suggesting is certainly logical, albeit somewhat scary.

    In SQL 2k5, job cache has plenty of issues. I've run into it several times where I've disabled a job and the job still ran because the cache hadn't updated. I'm not the only one with this problem, either. That's why I have the code on hand. @=)

    I don't know that enabling had the same problem, but to be safe, I always run the code again after re-enabling a job.

    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 Tarvin (2/15/2011)


    David Benoit (2/15/2011)


    Of course, according to BOL it is supposed to update the cache with every change but it may not be happening here. I haven't heard of the job cache having issues in the past but what Brandie is suggesting is certainly logical, albeit somewhat scary.

    In SQL 2k5, job cache has plenty of issues. I've run into it several times where I've disabled a job and the job still ran because the cache hadn't updated. I'm not the only one with this problem, either. That's why I have the code on hand. @=)

    I don't know that enabling had the same problem, but to be safe, I always run the code again after re-enabling a job.

    I'll definitely be saving the information off as well. Thanks!

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Is there a MSFT article which points to this? Thanks

  • sqldba_icon (2/15/2011)


    Is there a MSFT article which points to this? Thanks

    I had looked for a few minutes after Brandie posted this and didn't find anything but I'm sure there is something out there. The following note from BOL is pretty interesting though;

    "Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last."

    Notice that it has you running sp_add_jobserver LAST as that is when the job is actually cached. So, I'm wondering if that particular step is not being run by the GUI.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • ...and now I'm wondering if you were to trace your activity that you perform with the GUI if you would see that step being executed.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 15 posts - 1 through 15 (of 16 total)

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