2

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?

Tim Joseph
  • 21
  • 2

2 Answers2

1

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.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
0

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.

Chris W
  • 2,670
  • 1
  • 23
  • 32