How to create SQL Server Agent Jobs

SQL Server agent can be used to create JOBS which can be scheduled to execute automatically. SQL Server Agent uses SQL Server to store job information. Jobs can have one or more job steps. Each step contains its own task.Each action in a job is a job step.We can schedule the job based on need as daily, weekly, monthly, hourly etc

For example, Let us create a job to back up a database automatically.

Steps to create Job in SQL Server Agent

1. Expand SQL Server Agent in Object Explorer.(If SQL Server Agent is stopped ,right click and start it)

image

2.Right click Job->New job.

image

3.Fill  the Name field with a  name for your job,Give description.

image

4. On the left side of the New Job window, you’ll see a Steps icon under the Select a page.Next add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window.

image

  • Use the Step Name textbox to provide a name for the Step. select the database where the job has to be applied.
  • Use the Command textbox to give the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.
  • Validate the syntax, click OK to create the step.

image

5.Schedule the job by clicking the Schedule icon in the Select a Page.Then schedule new job.

image

6.Create alerts for your job by Giving alert name and also select the database.Also select the type of alert and severity of your alert

image

7.Specify the actions to be performed when the job completes or when job fails.

image

Note :All the above steps can be performed by TSQL (with out the GUI) using follow stored procedures

sp_add_job to create a job.
sp_add_jobstep to create one or more job steps.
sp_add_schedule to create a schedule.
sp_attach_schedule to attach a schedule to the job.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s