Automating Maintenance with Job Scheduling

Many of the maintenance features discussed so far in this chapter are important, but they can be time-consuming. However, that doesn't necessarily have to be the case. Essentially any task that can be performed from SQL Server Management Studio or through the use of a T-SQL query can be automated through the creation of a job or a schedule.

Wouldn't it be nice to have an agent working on your behalf to get the regular database maintenance activities done on a scheduled basis? Of course, the agent would have to provide feedback when things didn't quite go as expected. It would also be essential for summaries to be received from time to time, perhaps by email, providing documentation of exactly what had happened, when it was performed, and what activities failed. Fortunately, SQL Server framework has just such functionality: the SQL Server Agent.

The SQL Server Agent runs as a Microsoft Windows service. When running, the agent executes scheduled tasks. Each task is defined within SQL Server as a job. The agent performs the steps defined in the job and stores the job information and history within the msdb database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

Exam Alert

For the 70-431 exam, you will not be expected to perform the coding necessary to create individual job steps. The focus of the exam in this area is more on ensuring that jobs are executed on schedule. You need to know how to query msdb, when and why you would perform the query, and what information you can obtain. In particular, you need to know what processes are available to obtain similar information without executing queries directly against msdb.

Most jobs contain multiple steps, although it is possible for a job to be made up of a singular process. You define a workflow to instruct the agent on how to proceed between the individual tasks. The workflow allows for decision making in a job with a variety of outcomes, based on the results of completing or not completing steps within the job.

Viewing Job Details and History

Whereas most object information is stored in the master database in SQL Server, when you create a job, the information about the job is stored in several tables of the msdb database. You could query these tables, like any others, to find information about the jobs in the system. Among other tables used in msdb, the most useful are the following, which store the job, schedule, and history information. SELECT operations can be performed on the following objects to acquire information on jobs and schedules that have been created.

• msdb..sysjobs Stores information for jobs created.

• msdb..syschedules Contains information about schedules.

• msdb..sysjobschedules Stores schedule associated job information.

• msdb..sysjobhistory Contains the history of job executions.

Although you can query these tables directly or you can formulate a complex join query to obtain the information you want, it is far easier to use the existing system stored procedures that have already been formulated for that purpose. The following stored procedures can be used to provide information about jobs and related schedule objects.

• sp_help_job Returns information about all jobs created.

• sp_help_jobschedule Returns information about jobs and their associated schedules. (You provide a job ID or job name as an input parameter.)

• sp_help_jobhistory Returns information about the historical execution of jobs. (You provide the job ID or job name as an input parameter.)

It is easiest to view the job information from within SQL Server Management Studio. You can view job history by right-clicking a job name and then selecting View History. You can even see step breakdown information in Server Management Studio if you expand the details of a step. To view schedule information for a specific job, you simply double-click the desired job and then select the Schedules page. A sample schedule is shown in Figure 6.5.

Figure 6.5. The Job Schedule Properties dialog.

Jobs are flexible mechanisms and can be quite complex. The scope of the 70-431 exam is not on the creation of jobs but on being able to maintain any jobs that are already on the server. For more information on SQL Server Agent, jobs, and the details of their use, consult SQL Server Books Online.