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.

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.

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

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.

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.

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.

Filed under: Microsoft SQL Server, SQL Agent Jobs, SQLServerPedia Syndication Tagged: microsoft sql server, SQL Agent Jobs