Professional SQL Server™ 2005 Programming

24.1. Scheduling Jobs

Many of the tasks that we'll go over in the remainder of the chapter can be scheduled. Scheduling jobs allows you to run tasks that place a load on the system at off-peak hours. It also ensures that you don't forget to take care of things. From index rebuilds to backups, you'll hear of horror stories over and over about shops that "forgot" to do that, or thought they had set up a scheduled job but never checked on it.

If your background is in Windows Server, and you have scheduled other jobs using the Windows Scheduler service, you could utilize that scheduling engine to support SQL Server. Doing things all in the Windows Scheduler allows you to have everything in one place, but SQL Server has some more robust branching options.

There are basically two terms to think about: jobs and tasks.

• Tasks — These are single processes that are to be executed, or batch of commands that are to be run. Tasks are not independent — they exist only as members of jobs.

• Jobs — These are a grouping of one or more tasks that should be run together. You can, however, set up dependencies and branching depending on the success or failure of individual tasks (for example, task A runs if the previous task succeeds, but task B runs if the previous task fails).

Jobs can be scheduled based on:

• A daily, weekly, or monthly basis

• A specific time of the day

• A specific frequency (say, every 10 minutes, or every hour)

• When the CPU becomes idle for a period of time

• When the SQL Server Agent starts ...