Managing SQL Server Jobs

Using jobs (formerly known as tasks) to automate processes is an effective way to keep your server and databases functional and running well. Jobs are a way of automating the system administration work that doesn’t change from day to day, a way of handling the repetitive chores that can eat up your time. Some things you might set up a job to do include checking log sizes, monitoring performance, and making backups of database and transaction logs. I’ll show you a few tools, including automated job monitoring and the job history, that can make managing your jobs easier and leave you free to deal with unexpected problems and the long-term strategy of database administration.

Job Maintenance

After you plan and create your job, you need to maintain it. (See "Using SQL Server 7.0 Jobs," April 1999 WebSQL exclusive, for details on job creation and scheduling.) Here are a few basic maintenance tools to use.

Starting and stopping jobs. The Start and Stop commands let you run a job without changing the schedule. The commands to start and stop a job are in the Jobs Context menu in Enterprise Manager. Also, you can run them from within T-SQL with sp_start_job, which runs a specified job even if it’s disabled, and sp_stop_job, which stops a specific job.

You can stop a job while it is executing. In most cases, when you issue a Stop command, it cancels the current job step and ignores any retry logic, even if the Retry option is set. You cannot stop some steps, such as some database consistency checker (DBCC) commands and BACKUP. Because many maintenance jobs are likely to use such commands, pay careful attention to job functions before stopping anything.

Disabling jobs. All scheduled jobs are enabled by default, so to prevent a job from running according to schedule, you must disable it. Even if a job is disabled, it can still execute in response to an alert or when a user issues the Start command. If you have disabled a job and definitely do not want it to run, delete it, unless you have your environment under very tight control.

Be aware that SQLServerAgent automatically disables schedules that are no longer valid. For example, it disables schedules if they are to run once at a specific date and time and that time has passed, or if they are defined to run on a recurring schedule and the end date has passed. If you edit a schedule after SQLServerAgent has disabled it, you must re-enable it manually.

Modifying jobs. After you create a job, you can view the job definition; then you can delete or edit the job. You may need to add (or delete) a database in the backup job, or change the schedule for operational reasons. Editing allows modification of all options, including ownership.

Automated Monitoring and Notifications

For daily maintenance, regular and frequent monitoring of essential jobs is crucial to keeping your system running well. After you define a job to execute one or more times, you need to monitor that job for success or failure each time it executes. Because jobs can be executed on one local server or on multiple remote servers, by one or more alerts, and according to one or more schedules, automated monitoring and notification can help greatly in managing your jobs.

For non-urgent work, logging the output from jobs can supply useful information that you can use to recover from a problem as soon as you discover it. You can write the result of a job’s outcome to the Windows NT event log. This approach is useful because many event-tracking packages use the event log, and the event log will disseminate information about a job’s actions to a wider monitoring audience. You might even want to create a job to check on all output logs.

Here’s how to write the job status to the NT application event log (Enterprise Manager).

From the Details pane, right-click the job, then click Properties. In the Job Properties dialog box, click the Notifications tab. The result appears in Screen 1. Now select Write to Windows NT application event log, and click

SQLServerAgent is another useful monitoring tool. It monitors both itself and SQL Server service. SQLServerAgent starts the extended stored procedure xp_sqlagent_monitor (SQL Server Agent Monitor) to monitor the SQLServerAgent service to ensure that it is available to execute scheduled jobs, raise alerts, and notify operators. The SQLServerAgent is not crucial to the functionality of SQL Server and might not even need to be running, but it is required for successful system monitoring. Also, it can notify operators of problems and triggers alerts where set. If the SQL Server Agent Monitor is not running, the SQLServerAgent will not restart. If the SQLServerAgent service terminates unexpectedly, the SQL Server Agent Monitor restarts the service. Automated restart is enabled by default, so if you are using SQL Server clustering, you must disable auto-restart for failover to work. (See Brian Moran and David Sapery, "SQL Server Clustering," June 1999, for details on clustering.) To disable auto-restart, clear the appropriate box on the Advanced tab of the SQL Server Agent Properties dialog box, which Screen 2 shows. SQLServerAgent can restart the local server running SQL Server if SQL Server has terminated for reasons other than a normal shutdown. SQLServerAgent writes to the application event log and restarts SQL Server when it detects abnormal termination. This functionality lets you set an alert on this event.

You can define alerts to respond to SQL Server events by notifying operators (as Screen 3 shows), by raising an SNP trap, by forwarding the event to another server, or by executing another job. Also, you can create an alert to monitor for undelivered or failed notifications.

Job History

Most DBAs use job histories during all maintenance work, and especially when troubleshooting job problems. After you have executed a job, you can view its history. The job must have executed at least once for a job history to exist.

Screen 4 shows the history of a bank’s transaction check job run on the Customers database. If you are using T-SQL, you can also list the history of a specific job by running sp_help_jobhistory. To view the job history via Enterprise Manager:

As Screen 5 shows, you can use the Job System tab of the SQL Server Agent Properties dialog box to limit the total size and the size per job of the job history log. The job history in Enterprise Manager is enabled by default to show details, including the result, from each job step. You can change the default to show only the final outcome of the job.

Getting Information About a Job

Besides the job history, three other sources supply job information; the one you choose depends on your server configuration, SQL setup, and any problems you might be investigating. The main job information source is the Enterprise Manager, where all jobs and their information are displayed. The second source is via T-SQL stored procedures such as sp_help_job, which supplies information about a specific job.

The third source of job information is the system tables. SQL Server stores all information about objects, including jobs, in system tables. To view this information, you must define the system tables to be viewable in the configuration. The most useful tables are Syscategories, which describes all categories defined for the local server; Sysjobs, which contains all the core definitions for each job; Sysjobschedules, which holds all the schedule information; and Sysjobsteps, which holds the execution attributes for the steps within a job. For a bank’s transaction check job, the Syscategories table might hold credit card data, business accounts, etc. Sysjobs holds the descriptive information such as name, description, and owner. In procedures with few job steps, Sysjobsteps will have only a few entries. Table 1, which is also in SQL Server’s MSDB database, is the system table that relates to jobs.

And Finally

Jobs are a simple, easy-to-understand part of SQL Server 7.0, and they have the potential to vastly improve a DBA’s workaday life. They can be as powerful and complex or as simple as you choose, and they enable you to manage your servers more automatically.