sqlserveragent - SQL Server Agents jobs and turning off the server

24
2014-04
  • Tim Joseph

    I'm really new to SQL Agent jobs, but I am attempting to build up a maintainance regime for a server that will be turned off and on again at unknown intervals. It may run without being shutdown for a month, or it might only be turned on 9-5... we don't know and the client can't tell us because they don't know.

    So what I'm wondering is, what do I need to do to get SQL Server to run monthly and daily jobs either when they are due, or if the due date is missed, get them to be run when the server is next powered on. I could come up with a mish-mash of periodic jobs and 'on-power-up' jobs, but if there is something more elegant that would be wonderful.

    Obviously I'll need to ensure the SQL Server Agent is configure to start when the computer is powered up, but what else?

  • Answers
  • Nick Kavadias

    Your options for sql server agent schedule events are:

    1. Whenever SQL Server Agent starts.
    2. Whenever CPU utilization of the computer is at a level you have defined as idle.
    3. One time, at a specific date and time. On a recurring schedule.

    If you need something that handles more events then you can always do something like use the Windows scheduler and call sqlcmd that executes sp_start_job or could even execute the BACKUP DATABASE commands directly.

  • Chris W

    Schedule the jobs as normal and have a single job set to run when the server starts which runs a script to query all the jobs and determine any that were missed and tell them to execute now is an option - but clearly it depends on how the server is used and whether the jobs will impact users.

    If the box is getting turned on and needs to perform immediately would you really want all of those catch-up jobs getting fired immediately? You're script could re-schedule them for the next period of quiet time (e.g. that evening) but from what you've said the box may get turned off again before that window comes up anyway.


  • Related Question

    sql server 2005 - SSRS - SQL Agent Job Report Queuing
  • uzbones

    So we have a small number of SQL Server 2005 Servers running a lot of SSRS reports, which is running into issues where the servers cannot keep up with the total number of reports, that users are scheduling on shared schedules, running at once.

    Has anybody seen or know of a good way to implment some sort of job queues or lists?

    For instance a '6 am' job list that will contain all of the reports that run at 6 am, and then one agent job will start running the reports in the list one after the other. This would keep the reports from running thier mini-DDOS attack against thier own reporting servers.

    We would prefer that we not have to remove the scheduling functionalty that is already built into SSRS, we just want the agent to not kick off 3000+ reports all at the same time, without having to manually schedule each one to run 1-2 seconds from the previous one.


  • Related Answers
  • Jason Horner

    I have similar problem. I created "banks" of shared schedules to handle this.

    For example

    I have 1 "bank" setup to run at 6am another to run at at 6:05 etc. Then I make sure that the reports are balanced across each "bank".

    Another option to look into is Resource Governor (Requires sql server 2008). It won't queue up the jobs; but it will allow you to ensure that they don't bog down the server:

    [Resource Governor on BOL[1]