Post reply

SQL JOBS

  • April 8, 2012 at 6:46 am

    #391970

    Hi,

    I just need to disable sql jobs during holidays.

    I found few ways for doing that but I don't know which is more efficient and generalized method.

    Kindly provide your suggestion to make this happen.

  • April 8, 2012 at 9:13 am

    #1470522

    mdkmohan (4/8/2012)

    Hi,

    I just need to disable sql jobs during holidays.

    I found few ways for doing that but I don't know which is more efficient and generalized method.

    Kindly provide your suggestion to make this happen.

    I would disable/enable the jobs using a script that you can even schedule as needed, check here http://msdn.microsoft.com/en-us/library/ms186976.aspx#TSQL

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • April 8, 2012 at 9:23 am

    #1470523

    mdkmohan (4/8/2012)

    Hi,

    I just need to disable sql jobs during holidays.

    I found few ways for doing that but I don't know which is more efficient and generalized method.

    Kindly provide your suggestion to make this happen.

    No doubt it's considered bad practice, but a quick solution is to manually move the jobs' start dates into the future (to the date when you want them to start running again).

    If you have a lot of job to maintain, a scripted solution is clearly better.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 8, 2012 at 10:23 am

    #1470524

    Thnx buddy:-)

  • April 8, 2012 at 10:25 am

    #1470525

    Hi Phil,

    Can we automate this one. I mean changing the start date.

  • April 8, 2012 at 3:00 pm

    #1470541

    If you want to disable ALL jobs quickly and easily, just turn off SQL Agent.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 8, 2012 at 3:06 pm

    #1470542

    Jeff Moden (4/8/2012)

    If you want to disable ALL jobs quickly and easily, just turn off SQL Agent.

    I do not think poster wants to stop backup processes.

    On the other hand, if poster really don't want to be bothered next best option is to turn off the physical server, and let the application respond with a "went fishing" message 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • April 9, 2012 at 3:06 am

    #1470581

    Hi Mohan,

    Open SSMS

    Right Click on the Job you want and from the POP Up select Disable option.

    To check after its been disabled the Job will be having an Red arrow pointing downwards.

    If you want to Enable it again right Click on it and select Enable.

    Its the easiest way.

  • April 9, 2012 at 6:17 am

    #1470611

    ard5karthick (4/9/2012)

    Hi Mohan,

    Open SSMS

    Right Click on the Job you want and from the POP Up select Disable option.

    To check after its been disabled the Job will be having an Red arrow pointing downwards.

    If you want to Enable it again right Click on it and select Enable.

    Its the easiest way.

    Yes, this is both the easiest... and the wrongest way to do it on a production environment - all and any changes to a production environment should be done via scripts, only way to be sure what was done.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • April 9, 2012 at 7:43 pm

    #1471029

    Hi All,

    Thanks for your replies.

    My idea is to have a table with the list of holidays (dates)

    A sql job which is to be scheduled everyday at 00:01 with a SP

    stored procedure for disabling the jobs based on the date in the holiday list table.

    Is there any other most efficient way to implement this.

  • April 10, 2012 at 1:11 am

    #1471082

    That would be a realistic way 🙂 U needn't worry about restarting the jobs whenever you are back or needn't put an reminder for every holiday.

  • April 10, 2012 at 11:08 am

    #1471371

    Yes, that's my idea but my only concern.

    Is there any better way to do this ?

  • April 10, 2012 at 9:55 pm

    #1471659

    Not able to find simpler than this:(

Viewing 13 posts - 1 through 12 (of 12 total)

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