SQL Server Agent allows you to automate various administrative tasks. In this tutorial, we will walk through the process of using SQL Server Agent to create and schedule a task that automates database management. This guide only applies to SQL Server 2012. If you are using an earlier version of SQL Server, you can read Automating Database Management with SQL Server Agent. If you are using a later version of SQL Server, you can read Configure SQL Server Agent for SQL Server 2014.
- Running SQL Server Agent in SQL Server 2012
- Switch to SQL Server Management Studio
- Create SQL Server Agent Job
- View work steps
- Create work step
- Scheduling a SQL Server 2012 Agent Job
Running SQL Server Agent in SQL Server 2012
Open the Microsoft SQL Server Configuration Manager and click the “SQL Server Services” item in the left pane. Then, in the right pane, locate the SQL Server Agent service. If the status of this service is “RUNNING”, you don’t need to do anything. Otherwise, right-click the SQL Server Agent service and select Start from the pop-up menu. The service starts.
Switch to SQL Server Management Studio
Close SQL Server Configuration Manager and open SQL Server Management Studio. In SSMS, expand the SQL Server Agent folder. You will see the expanded folders shown above.
Create SQL Server Agent Job
Then right-click on the Jobs folder and select New Job from the start menu. You will see the new job creation window shown above. Fill in the Name field with a unique title for your job (descriptive information will help you better manage your job in the future!). Enter the account you want to take ownership of the task in the Owner text box. The task runs with the permissions of this account and can only be modified by members with the Owner or System Administrator role.
After you define the name and owner, select one of the predefined job categories from the drop-down list. For example, you can select the Database Maintenance category for scheduled maintenance activities.
Use the large Description text box to provide a detailed description of the purpose of your work. Write it in such a way that someone (including you!) can look at it in a few years and understand the purpose of the work.
Finally, make sure the checkbox is checked.
Don’t click OK yet – we still have a lot of work to do in this window!
View work steps
On the left side of the New Task window, you will see the Steps icon under the “Select Page” heading. Click this icon to see the empty list of above task steps.
Create work step
Then you need to add individual steps for your work. Click the New button to create a new task step and you will see the above New Task Step window.
Use the Step Name text box to enter a descriptive name for the step.
Use the Database drop-down list to select the database that the job will work with.
Finally, use the “Command” text box to specify the Transact-SQL syntax that corresponds to the desired action for this task step. After you are done entering the command, click the Parse button to check the syntax.
After a successful syntax check, click OK to make the step. Repeat this process as many times as necessary to determine the desired SQL Server Agent job.
Scheduling a SQL Server 2012 Agent Job
Finally, you want to set a schedule for the task by clicking the Schedule icon in the Page Selection section of the New Task window. You will see the New Task Schedule window above.
Enter a name for the schedule in the Name text box and select the type of schedule from the drop-down list (Single, Recurring, Run on SQL Server Agent startup, or Run on CPU idle). Then use the Frequency and Duration sections of the window to specify task options. When you are done, click OK to close the Schedule window and click OK to create the task.