Automate Database Administration With SQL Server Agent

Graphical representation of a database
Graphical representation of a database.

 luismmolina/Getty Images

 SQL Server Agent allows you to automate a variety of administrative tasks. One of those tasks involves using SQL Server Agent to create and schedule a job that automates database administration.

of 06

Start the SQL Server Agent Service

Screenshot of SQL server Configuration Manager

Open Microsoft SQL Server Configuration Manager and locate the SQL Server Agent service. If the status of that service is "RUNNING," you do not need to do anything. Otherwise, right-click on the SQL Server Agent service and select Start from the pop-up menu to open the Starting Service window.

This article applies to SQL Server 2008. If you are using a later version of SQL Server, you may want to read Configuring SQL Server Agent in SQL Server 2012.

of 06

Open SQL Server Management Studio and Expand the SQL Server Agent Folder

SQL Server Agent folder screenshot

Close SQL Server Configuration Manager and open SQL Server Management Studio. Within SSMS, expand the SQL Server Agent folder. 

of 06

Create a New SQL Server Agent Job

New Job screenshot

Right-click on the Jobs folder and select New Job from the start-up menu. Fill in the Name field with a unique name for your job (being descriptive will help you manage jobs better down the road). Specify the account that you want to be the owner of the job in the Owner text box. The job will run with the permissions of this account and may only be modified by the owner or sysadmin role members.
After you specify a name and owner, choose one of the predefined job categories from the drop-down list. For example, you might choose the "Database Maintenance" category for routine maintenance jobs.
Use the large Description text field to provide a detailed description of the purpose of the job. Write it in such a way that someone (yourself included) would be able to look at it several years from now and understand the purpose of the job.
Finally, ensure that the Enabled box is checked.

of 06

Enter the SQL Server Agent Job Steps Screen

New Job screenshot

On the left side of the New Job window, you'll see a Steps icon under the "Select a page" heading. Click this icon to see the blank Job Step List.

of 06

Add SQL Server Agent Job Steps

New Job Step screenshot

Add the individual steps for the job. Click the New button to create a new job step and you will see the New Job Step window.
Use the Step Name text box to provide a descriptive name for the Step.
Use the Database drop-down box to select the database that the job will act upon.
Finally, use the Command text box to provide the Transact-SQL syntax corresponding to the desired action for this job step. After you complete entering the command, click the Parse button to verify the syntax.
After successfully validating the syntax, click OK to create the step. Repeat this process as many times as necessary to define the desired SQL Server Agent job.

of 06

Schedule the SQL Server Agent Job

New Job Schedule screenshot

Set a schedule for the job by clicking the Schedule icon in the Select a Page portion of the New Job window. You'll see the New Job Schedule window.
Provide a name for the schedule in the Name text box and choose a schedule type—One-time, Recurring, Start when ​SQL Server Agent Starts or Start When CPUs Become Idle—from the drop-down box. Use the frequency and duration sections of the window to specify the job's parameters. When you are finished, click OK to close the Schedule window and OK to create the job.