Scheduling jobs is one of the core SQL Server functions. Many businesses have numerous SQL Server jobs scheduled that perform any number of different tasks from database maintenance jobs like backup and index rebuilds to running queries and kicking off ETL tasks. The SQL Agent is the SQL Server subsystem that’s responsible for automatic task scheduling. The SQL Agent is available in all of the editions of SQL Server except the SQL Server Express edition. Taking advantage of SQL Agent enables you to automate many of your routine IT database infrastructure tasks.
SQL Agent Components
SQL Agent consist of four core components:
- Jobs – A SQL Agent job is the specified action or series of actions that the SQL Server Agent will execute.
- Schedules – The SQL Agent schedule specifies when a job will run. Multiple jobs can have the same schedule.
- Alerts – A SQL Agent alert is an automatic response to a specific system event.
- Operators – A SQL Agent operator defines contact information for the person who is responsible for the maintenance of a SQL Server instance.
Starting the SQL Agent Service
The SQL Agent service must be started before you can use it to schedule jobs. You can start the SQL Agent service either by using Windows Services or by using the SQL Server Configuration Manager. You can see how to start the SQL Agent service using SQL Server Configuration Manager in Figure 1.
- Figure 1 – Starting SQL Agent with SQL server Configuration Manager
To start the SQL Server Agent simply right click on it and select Start from the context menu. You can configure the SQL Agent by double clicking on the SQL Server Agent entry in SQL Server Configuration Manager. The Log On tab enables you to change the account that the SQL Agent service runs under. The Service tab enables you to set the service Start Mode to Automatic, Manual or Disabled. Typically, you would want the Start Mode to be Automatic so that the SQL Agent is always available when the system is running. There is also an Advanced tab that enables you to turn customer feedback and error reporting on and off.
Creating SQL Agent Job Steps
After the SQL Agent has been started you can create new SQL Agent jobs by opening SQL Server Management Studio (SSMS) and then right clicking on the SQL Agent node. Select New Job from the context menu to display the New Job dialog that you can see in Figure 1.
- Figure 2 – Creating a New Job: General page
The General page essentially allows you to give your SQL Agent job a name and an optional description that describes what the job does.
Clicking on the Steps page enables you to define one of more job steps or actions that the SQL Agent job will perform. When the Steps page is initially displayed it will be empty. You need to click on the New button to add a job step which will display the New Job Step dialog that you can see in Figure 3.
- Figure 3 – Creating a New Job: New Job Step page
The New Job Step dialog is the core of the SQL Agent job as it contains the actual commands that will be run by the job. In Figure 3, you can see that a single job step consisting of the T-SQL BACKUP DATABASE command has been created. Job steps can execute any of the following:
- ActiveX Script
- PowerShell
- Replication Distributor
- Replication Merge
- Replication Queue Reader
- Replication Snapshot
- SQL Server Analysis Services Command
- SQL Server Analysis Services Query
- SQL Server Integration Services Package
- Transact SQL script (T-SQL)
You can change the security context that the job runs under using the Run as dropdown. The Open button enables you to open a T-SQL file. The Copy and Paste buttons enable you to edit portions of the command text box. The Parse button enables you to parse the command and validate the syntax of the command.
You also have the option to control what happens on the success or failure of the job step by clicking on the Advanced page. The default action for a successful job step is to continue with the next step. You also have the options to quit the job and report a success or quit the job and report a failure. Likewise, if the job step has an error and it fails, then the default action is to quit the job and report a failure. You also have the options to continue on to the next job step or to quit the job and report a success.
After you’ve entered the command you can click the OK button to save the job step and return to the Steps page of the New Job dialog. At this point, you can optionally enter more job steps. You can also edit or delete the existing job steps.
Scheduling SQL Agent Jobs
After you’ve created the job and defined the job steps that will be executed the next step is typically to create a schedule for the job.
Clicking on the Schedules page enables you to setup a scheduled run time for the SQL Agent job. Like the Steps page, when the Schedules page is initially displayed it will be empty. Clicking on the New button will display the New Job Schedule dialog that you can see in Figure 4.
- Figure 4 – Scheduling a SQL Agent Job
To setup a new schedule you first need to give the schedule a name. Then you select the type of schedule you want. Most of the time you will want the schedule to be recurring but you can also set it to run whenever the SQL Agent starts, whenever the CPU becomes idle or to run as a one time job. For recurring jobs you also need to set the frequency for the schedule to be run. After you’ve setup the schedule the job is ready to run.
Running and Managing SQL Agent Jobs
You can use SSMS to manage your SQL Agent jobs by expanding the SQL Agent node and then right clicking on the job that you want to manage. From the context menu you can start jobs at a specific job step, script out the job and view the job’s run history. You can also use the context menu to delete or rename any of the jobs. If you want to display the status of any of the scheduled jobs you can open up the Job Activity Monitor. The Activity Monitor shows the all the scheduled jobs as well as their status, the last run date and the next run date.
Leave a Reply