Scheduled SQL Server Agent Jobs

  • Has anyone else seen scheduled jobs that are disabled still try running? I have a job that is disabled but it is still being fired off by its schedule.

  • Hi Lynn,

    I have seen that happen in SQL 2k when someone updates sysjobs directly by setting enabled = 0.

    Disabling a job using sp_update_jobs stops it correctly.

    The wierd part is that Enterprise manager says disabled but its not.

    I am not sitting in front of a SQL 2k8 box to check whether the same behaviour still persist. So not sure abt it.

  • Do you actually see the job being fired off on the server its disabled on?

    I ran into a sort of similar issue where a SSIS package was being run, despite the job that calls it being disabled. Turns out someone had restored the master db on a test practice machine, and that job was calling the package on the production machine and executing it there.

  • Yes, the job ran at 6:00 and 8:00 this morning when it should not have. It has gone past 10:00 and it didn't run this time. I'm not sure if it was something I did (enable/disable) or it just realized it was supposed to run. I will have to monitor this behavior further when we have a mirror failover again to our secondary server.

    All I can say, very weird.

  • Lynn,

    Do you have any other jobs or processes that may be trying to fire it off? I ask because I have a ton of jobs that are setup in that fashion. I inherited a ton of jobs that are disabled but there is a process somewhere that executes that disabled job.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (3/30/2010)


    Lynn,

    Do you have any other jobs or processes that may be trying to fire it off? I ask because I have a ton of jobs that are setup in that fashion. I inherited a ton of jobs that are disabled but there is a process somewhere that executes that disabled job.

    In this particular case, I know this is not the case. The job that was running was a transaction log backup scheduled to run ecery two hours. It ran at 4:00 AM like it was supposed to run, then whe experienced an unknown issue on the network at 5:07 that caused a mirror fail-over from the blade servers to the rack servers. As a part of this process, event notivations are captured on both (actually all four) systems that uses Service Broker to disable the database specific jobs on the failing sever(s) and to enable the corresponding database mirrored server(s) so that we don't miss any backups and so that we don't have numerous unwanted emails for backup jobs that fail because a database is in mirror/recovery state.

    Also had a problem with the transaction log backup job running on the mirror server. I had to redo the schedule, but not sure why. Testing didn't seem to have any problems earlier with this.

  • I think I may have solved this problem, we'll know for sure the next time the mirrors fail-over.

    SQL Server Agent caches job information. If you take a close look at msdb.dbo.sp_update_job you will find that this procedure notifies SQL Server Agent of changes to a job.

    Modified my SB routine to use this procedure to enable/disable database specific jobs.

    Thanks.

Viewing 7 posts - 1 through 7 (of 7 total)

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