Disable all the SQL jobs at once

  • Guras

    SSCertifiable

    Points: 6352

    We have several sql jobs ( about 10 jobs) that need to be disabled at 11:30 PM at the end of the month, each month in SQL server 2008. Is there a way to disable all the jobs at once. Currently I go to each job and I set the end date schedule as the last day of the month at 11:30 PM.

    I was hoping there should be a better way than this.

    Thanks for the help

  • Richard Moore-400646

    SSCrazy

    Points: 2274

    Go to the Job Activity Monitor and ctrl-left click on each, then right click and select disable job, or you can write a script against msdb.dbo.sysjobs and update the enabled column.

  • HowardW

    One Orange Chip

    Points: 29049

    Easy enough, Just use something like this to generate the statements:

    --generate disable

    SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs

    --generate enable

    SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs

    Then schedule SQL jobs from the results at the time you need

  • GSquared

    SSC Guru

    Points: 260824

    Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    GSquared (12/1/2011)


    Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.

    It will disable casual / on request tasks as well say Export / Import.

  • Cadavre

    SSC-Forever

    Points: 41582

    DECLARE @SQL AS VARCHAR(MAX)

    SELECT @SQL = COALESCE(@SQL+ + CHAR(13), '') + 'EXEC msdb..sp_update_job @job_name = ''' + name + ''', @enabled = 0;' FROM msdb.dbo.sysjobs

    EXEC (@SQL)

    I'd do what G-Squared suggested though.


    Forever trying to learn
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

    If you litter your database queries with nolock query hints, are you aware of the side effects?  Try reading a few of these links...
    (*) Missing rows with nolock
    (*) Allocation order scans with nolock(*) Consistency issues with nolock
    (*) Transient Corruption Errors in SQL Server error log caused by nolock
    (*) Dirty reads, read errors, reading rows twice and missing rows with nolock

    Craig Wilkinson - Software Engineer
    LinkedIn

  • Guras

    SSCertifiable

    Points: 6352

    I took the route of what HowardW sugested since I realized there were some notifications jobs that needed to be running.

    Here is my sp

    CREATE PROCEDURE [dbo].[PROC_SQLJOBS_ENABLE_DISABLE] (@enableFlag int)

    AS

    /** ***** Version *****

    * $Revision:$

    * $Date: $

    * $Author $

    * $Archive:$

    *

    * Sample use: exec PROC_SQLJOBS_ENABLE_DISABLE 0

    * Comments : This stored procedure disables or enables all the listed jobs in the sp at once

    * @enableFlag = 0 Disable jobs

    * @enableFlag = 1 Enable jobs

    */

    If @enableFlag = 0 --disable jobs

    BEGIN

    exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0

    exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 0

    END

    If @enableFlag = 1 --enable jobs

    BEGIN

    exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 1

    exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 1

    END

    GO

    Now taking this to the next level I want to capture error msg from each step if any and send out email to the admin

    Any help would be greatly appreciated.

    Thanks

  • GSquared

    SSC Guru

    Points: 260824

    Dev (12/1/2011)


    GSquared (12/1/2011)


    Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.

    It will disable casual / on request tasks as well say Export / Import.

    Nope. Just tested it (SQL 2008 R2 Dev Edition). Turned off SQL Agent, then used the Export wizard to export a table to a text file. Worked as expected.

    Some things, like the Copy Database Wizard, depend on SQL Agent jobs, but not usual Export/Import wizards.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared

    SSC Guru

    Points: 260824

    Guras (12/1/2011)


    I took the route of what HowardW sugested since I realized there were some notifications jobs that needed to be running.

    Here is my sp

    CREATE PROCEDURE [dbo].[PROC_SQLJOBS_ENABLE_DISABLE] (@enableFlag int)

    AS

    /** ***** Version *****

    * $Revision:$

    * $Date: $

    * $Author $

    * $Archive:$

    *

    * Sample use: exec PROC_SQLJOBS_ENABLE_DISABLE 0

    * Comments : This stored procedure disables or enables all the listed jobs in the sp at once

    * @enableFlag = 0 Disable jobs

    * @enableFlag = 1 Enable jobs

    */

    If @enableFlag = 0 --disable jobs

    BEGIN

    exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0

    exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 0

    END

    If @enableFlag = 1 --enable jobs

    BEGIN

    exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 1

    exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 1

    END

    GO

    Now taking this to the next level I want to capture error msg from each step if any and send out email to the admin

    Any help would be greatly appreciated.

    Thanks

    Try...Catch and sp_send_dbmail are pretty much built for that. Are you familiar with those?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    GSquared (12/1/2011)


    Dev (12/1/2011)


    GSquared (12/1/2011)


    Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.

    It will disable casual / on request tasks as well say Export / Import.

    Nope. Just tested it (SQL 2008 R2 Dev Edition). Turned off SQL Agent, then used the Export wizard to export a table to a text file. Worked as expected.

    Some things, like the Copy Database Wizard, depend on SQL Agent jobs, but not usual Export/Import wizards.

    I was assuming (in fact I was very confident) that Copy Database & Export / Import Wizard share same logic (common classes inside) and same requirements. It should fail if you try to export / import to other RDBMS or SQL Server.

    For Text File export I believe it uses BCP internally so it doesn’t require SQL Agent.

  • Guras

    SSCertifiable

    Points: 6352

    I did something like this

    Declare @errMsg varchar(1000)

    If @enableFlag = 0 --disable jobs

    BEGIN

    BEGIN TRY

    exec msdb..sp_update_job @job_name = 'Update_job1', @enabled = 0

    END TRY

    BEGIN CATCH

    SET @errMsg = 'There was an error! ' + ERROR_MESSAGE()

    END CATCH

    BEGIN TRY

    exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 0

    END TRY

    BEGIN CATCH

    SET @errMsg = 'There was an error! ' + ERROR_MESSAGE()

    END CATCH

    If @errMsg <> ''

    Begin

    declare @rc int

    exec @rc = msdb.dbo.sp_send_dbmail

    @profile_name = 'SQLMail@thecompany.com',

    @recipients = 'dataExchange@thecompany.com',

    @subject = 'Job Failure - to enable/Disable jobs',

    @body = @errMsg,

    @body_format ='HTML'

    select @rc

    End

    END

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714297

    I like what Guras has done. You never know what jobs will change, and you might not want new jobs disabled. Or maybe you do, but if you want specific items disabled during a time period, I'd script a disable for those, and then document this in a checklist somewhere.

  • GSquared

    SSC Guru

    Points: 260824

    Of course, the other option would be to put a first step in those jobs that would check the time/date, and cause the job to abort if run during the desired window.

    That way, you don't have to worry about "did I remember to turn those jobs back on?" and that kind of thing.

    Far too easy to be on vacation or something, and the person covering for you doesn't know to turn them off and back on. Or turns them off, gets distracted, forgets to turn them back on. Or to get distracted yourself and forget one or the other.

    But a first step that checks "am I supposed to run now?" and aborts the job covers that whole problem nicely.

    Simple enough to have a step generate an error if run during the "don't run now" window, and to set that step to "continue to next step" on success, and "quit job reporting success" on error. Or variations thereof.

    Another advantage to that is that it's semi-self-documenting. A replacement or vacation coverage person can open up the job itself and see a step in it that has that behavior, and has a description in the step of what it does and why.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Guras

    SSCertifiable

    Points: 6352

    GSquared (12/1/2011)


    Of course, the other option would be to put a first step in those jobs that would check the time/date, and cause the job to abort if run during the desired window.

    That way, you don't have to worry about "did I remember to turn those jobs back on?" and that kind of thing.

    Far too easy to be on vacation or something, and the person covering for you doesn't know to turn them off and back on. Or turns them off, gets distracted, forgets to turn them back on. Or to get distracted yourself and forget one or the other.

    But a first step that checks "am I supposed to run now?" and aborts the job covers that whole problem nicely.

    Simple enough to have a step generate an error if run during the "don't run now" window, and to set that step to "continue to next step" on success, and "quit job reporting success" on error. Or variations thereof.

    Another advantage to that is that it's semi-self-documenting. A replacement or vacation coverage person can open up the job itself and see a step in it that has that behavior, and has a description in the step of what it does and why.

    I see what you are saying GSquared and that is a briallant idea. This route lets me have a peaceful vacation as well w/o having to worry if the jobs are turned off/ON....I will try to put that in a script and add as the first step in each job that needs to be turned off EOM. However I have a question on this. Let's say I check the current time and date and if it is 11:30 PM EOM then abort the job with script

    exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0

    So the Step2 ( exec myupdateProc) will not fire right? I would think so since the job is disabled in Step 1 but just confirming.

    THANKS!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714297

    The idea from GSquared is nice, but it could be a problem as well if you don't document this and make sure alerts will not fire or someone will not open tickets if these jobs do not run.

    If it's only once a month, that's probably OK, but if you have more complex logic for more dates, you could easily create some level of chaos.

    That being said, I'd implement Gus' solution and use it as a template for future jobs that need to be disabled.

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

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