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

  • Hi,

    bit of an odd problem. Our SQL server (64-bit Standard Edition, SP3) appears to attempt to run some SQL jobs, based on their schedule, BUT the job is disabled??

    Has anyone had this problem or know how to resolve it ?

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Is the job attempting to execute based on the schedule or at a different time other than the schedule.

    Since a disabled Job can also be started manually, my guess is some tried to start is manually.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Check for any other sort of schedulers like web services etc.

  • Yeah the jobs are running based on their schedule and that I am aware of there is now web schedules.

    I do have SSRS configured, but this are only SQL Server jobs.

    The enabling / disabling of jobs occurs once a week, when we fail over a mirrored principle database to the mirror

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • The T-SQL I'm running as part of a scheduled job to enable / disable the job is as follows:

    USE msdb

    UPDATE sysjobs

    SETEnabled = 0

    WHERE[Name] LIKE 'DB Backup%';

    This appears to work fine and some weeks I don't have the problem I've mentioned...... am at a bit of a loss as it what it's doing.

    Do I need to clear some sort of cached SQL Agent job list??

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • What is that you are trying to achieve, you started this thread saying the disabled jobs are running who is it possible?

    You say you are running T-SQL script that runs weekly to enable and disable jobs, so if you are doing it knowingly, what is that you are trying?? It baffles me.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • We are implemented a mirroring solution that fails over automatically week on week as a sort of automated DR test (so we know, that should we fail over when things go bad, then we won't get any nasty.... ' it's not working as it should...' issues).

    The plan is as follows:

    Week 1

    Server A is the Principle Server

    Server B is the mirror

    All services, backups and messaging are done with the Principle.

    Backups are only run on the principle for mirrored databases (for obvious reasons)

    Week 2

    Server B is the Principle Server

    Server A is the mirror

    During switchover at the start of week 2. Server A fails over the databases. It also disables the backup jobs.

    On Server B the backup jobs are enabled.

    The problem is, given the scenario, is that during week 2, Server A backup jobs are disabled, however SQL Server Agent seems to think that the job should be run and I am therefore getting the subsequent job failed errors as it can't backup a database that is in the mirroring state.

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • How about you script out the jobs and create them on the server that is the primary and delete them from the secondary. When you do your failover then delete from the new secondary and create them on the new primary. I guarantee you that a job that doesn't exist cannot run! 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Well scripting out and re-creating seems a little over-kill to me.

    However, it has lead me onto somthing else. looking at MSDN, SQL agent re-caches a job following a modification, when sp_add_jobserver is called.

    So it's looks like my issue to related to the job being cached on server. Now the question is, is there simple a way I can re-cache the jobs once a week?

    I'm thinking of simply restarting SQL Agent once a week.

    - however is there a stored procedure for enabling / disabling a job that would implicitly re-cache the jobs?

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • sp_update_job, although I have no idea about this 'job caching' business.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Hi thanks.

    I gone down the route of using the stored procedure and it would seem to now work as I expected it to.

    As I need to disable a few jobs, I've had to build this into a cursor, but it seems to work well now.

    🙂

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Not absolutely sure if this is the same issue, but from the initial post, we have had the same issue (on a standalone SQL2000 RTM install). A job exists with a schedule. The job is disabled but the schedule is not. The job then continues to run, on the schedule, until the schedule itself is disabled. I saw in an MS Connect post that restarting SQL Agent would also fix the problem; maybe this is related to the 'cache' mentioned? I've never heard of a cache here but I've only worked with SQL since 6.5 (13 years).:-D

  • well - I'm not a 100% on the cache. It would just seem that the issue we've had is related to how we update the job.

    From experience on other systems, for me, it points to the job details being stored in some sort of memory, if we are restarting the schduler and it then works (which is does).

    - I'm not an expert - it's just my 2 pence worth and my thoughts... 🙂

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

  • Found an update. SQL Server, in the sp_update_job, runs "exec msdb.dbo.sp_sqlagent_notify @op_type = N'J', @job_id = @job_id, @action_type = N'U'" which resets the cache for this Job_ID. So there is a cache. We've had issues setting the flag manually in SSMS, so it look like that might directly update the sysjobs table. Only sp_update_job seems to do it properly.

    Always something new to learn!

  • ain't that the truth! 😀

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

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

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