SQL Views: Steps to Setting Up SQL Agent

SQL Server Agent is SQL Server's built-in job scheduling program. SQL Server Agent enables you to automate operations by running jobs on one or more systems according to a predetermined schedule or predefined alerts. For example, you can use SQL Server Agent to run backups or database maintenance jobs, or you might use it to run nightly extraction, transformation, and loading (ETL) jobs using Integration Services.

Related: DBAs and the SQL Server Agent

SQL Server Agent isn't available in the SQL Server Express or Workgroup editions. To use it, you need SQL Server 2008 Standard, Enterprise, or Datacenter editions, or SQL Server 2012 Standard, Business Intelligence, or Enterprise editions. Here are the steps to get SQL Server Agent up and running; these steps are for SQL Server 2012 but are virtually identical for SQL Server 2008.

1. Start the SQL Server Agent Service—SQL Server Agent doesn't start by default. Open Windows Services and look for SQL Server Agent (MSSQLSERVER). If you have multiple SQL Server instances, you will see multiple SQL Server Agent entries for each instance. To start SQL Server Agent, right-click the service and select Start from the context menu. To set it to automatically start when Windows starts, right-click the SQL Server Agent service, select Properties from the context menu, then change the service's Startup Type to Automatic. Microsoft recommends that you use domain account privileges to start the SQL Server Agent service.

2. Add a SQL Server Agent Job—After the SQL Server Agent service is running, you're ready to begin creating SQL Server Agent jobs. To create a new SQL Server Agent job, open SQL Server Management Studio (SSMS) and expand the SQL Server Agent node. Next, right-click the Jobs node and select New Job from the context menu. This opens the New Jobs dialogue box. Assign the job a name, a category, and a description. If you want the job to be able to run, be sure to select the Enable check box. After creating the job, click the Steps navigation page to add steps to the SQL Server Agent job.

3. Create the Required Job Steps—A single SQL Server Agent job can have multiple job steps. You create a job step by clicking the New button on the Steps navigation page and filling out the New Job Step page, where you enter the job step name, type, command, and database. You also have the option to select the Advanced page to specify actions to take on success or failure of each job step. After adding the job steps that you want, click the Schedules page to specify the job schedule.

4. Add an Optional Schedule—You can execute SQL Server Agent jobs according to a predefined schedule. To add a recurring schedule, click the Schedules page, then click New to display the New Job Schedule page. This page lets you specify the job schedule name, recurrence, date and time, as well as start and end dates for the job. Again, be sure to select the Enable check box if you want the schedule to be active.

5. Add Optional Alerts—You can also configure SQL Server Agent jobs to execute when a given performance condition is met. To schedule jobs using alerts, click the Alerts page, then click Add to add a new alert. This opens the New Alert dialogue box, where you can specify which events will trigger an alert, such as a given performance counter value or an error number. The Response page lets you specify the job that will be run in response to the alert.

6. Monitor Your Job's Progress—You can see the status of the SQL Server Agent jobs currently being executed by using SSMS and opening the SQL Server Agent node. Click the Job Activity Monitor node to open the Job Activity Monitor, which shows all of the currently running jobs. Double-clicking a job entry in the Job Activity Monitor will show you the definition for that specific job, including the category, job steps, and job schedule.

7. Check the SQL Server Agent Error logs—SQL Server Agent logs the outcome of its jobs in its Error Logs folder. To view the current set of SQL Server Agent error logs, expand the SQL Server Agent node, then expand the Error Logs node. Click the Current node to open the Log File Viewer. This will show all of the recent SQL Server Agent events.

Related: Semi-Advanced Logging Options for SQL Server Agent Jobs