Reusing schedules in SQL Agent

1

January 25, 2016 by Kenneth Fisher

I’m a big fan of automation. You get it written, tested, and scheduled, then you just sit back and monitor for unexpected problems (because if they are expected you fixed them right?). One common place for us SQL people to do our automation is SQL Agent. It’s built into SQL Server and while not the best job scheduling system I’ve ever seen it’s not bad at all.

One of the nice things about it, that to me is highly under-rated and under-utilized is the re-use of schedules. If you are automating something you create your job, test it carefully and then it’s time to schedule it.

Schedules1

We’ve just started with a new system so we start by selecting New. That brings up the New Job Schedule window where we can set up our first schedule.

Schedules2

As it happens for our first job we want it to run every weekday at 4am.

Schedules3

Over time we collect more and more schedules (and as it happens we start with a handful of schedules). At this point if we want to select an existing schedule for our job we can hit Pick. But if we just want to look them over without going through a job we can open up the Manage Schedules window.

Schedules4

From here we can see the list of schedules available to us. We can easily add new ones, delete, disable/enable or edit existing ones and even view/edit the list of jobs associated with a given schedule.

Schedules5

What you may have figured out by all this is that schedules and jobs are independent.

So you can:

  • Re-use a single schedule for multiple jobs.
    Be somewhat careful here. If you have a bunch of jobs starting at the same time you could overload your system.
  • Use multiple schedules for a single job.
    This will let you create very complex schedules.

 

Because you can re-use the schedules you want to be careful naming them. I’ve seen far to many schedules named Schedule 1 or Job Name Schedule. The first is non-descriptive and the second too specific (it would look silly as the schedule for another job with a different name). Give your schedules names like Every Weekday at 4AM or The third of the month at 6AM. This way when you go to pick the right schedule you know which one is which.

Remember earlier when I said agent isn’t the best scheduling system I’ve seen? There is one major thing that schedules can’t do which I’ll admit is a bit of a pain. There is no way to create a holiday schedule. So you can’t say Every weekday except on major holidays. Usually this is a fairly minor problem though and there are workarounds (using code in the job).

NOTE: I’ve had a couple of friends (Robert Davis (b/t) and Nic Cain (b/t)) point out that it’s very easy to have problems with shared schedules. Specifically someone could go in and change a schedule for one job and wind up changing the timing on a bunch of jobs. Or someone could end up deleting a schedule and all of a sudden none of those jobs are running anymore. Now I haven’t had this problem personally but these guys really know what they are talking about. So if you decide to use shared schedules make sure you have strict processes in place about how those schedules are managed.

One thought on “Reusing schedules in SQL Agent

  1. […] Kenneth Fisher talks SQL Server Agent schedules: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: