How To Create And Schedule Job Using SQL Server Agent

In this post, we walk through the step by step process of creating and scheduling a job using SQL Server Management Studio.
 

What is SQL Server Agent ?

SQL Server Agent is a component of Microsoft SQL Server which allows the database administrator (DBA) to schedule jobs and handles other automated tasks such as database backups etc.

It runs as a windows service so it can start automatically when the system boots or it can be started manually.

SQL Server Agent has support for operators and alerts, so that administrators can be notified, e.g. by email.
 

SQL Server Agent Components

SQL Server Agent has four component to define the tasks to be performed, when to perform the task and how to report the successful and failure of the task.

SQL Server Agent Component

  1. Jobs – A SQL Server Agent job is a specified series of action that SQL Server Agent will execute. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database.
  2. Schedules – A schedule specifies when a job runs. You can define as many as schedule for a job to be executed, also you can use a schedule for many different SQL Server Agent Jobs.
  3. Alerts – An alert is an automatic response to a specific event. An event can be a job that starts or system resources that reach a specific threshold. An alert can Notify or Run a job
  4. Operators – An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. SQL Server can notify operators of alerts through email.

So the above four components enables a DBA to automate their tasks. Now, Let’s create a job and schedule it.

 

Pre-Requisite:

  • SQL Server Agent should be running. If not, open the SQL Server Configuration Manager and start the service.

SQL Server Agent

  • To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role.
Using SQL Server Management Studio
1. Connect to the Database Server

Now, Start SQL Server Management Studio, connect to the server you want to create a SQL Server Agent job. Within SSMS, expand the SQL Server Agent folder. You’ll see the expanded folders as shown below:

SQL Server Agent Jobs Section

 

2. Create A SQL Server Agent Job

Next, Right click on Jobs and select New Job option. You will see the New Job window as shown below:

Create SQL Server Agent Job WIndow

Fill the job name (e.g : JobTest), owner, Category and Description of the purpose of your job.

Wait… Don’t Click OK, We have more step to configure in this window like Steps, Schedules, etc. Follow the below Step3.
 

3. Create A Job Step

Next, Go to Steps sections. We need to add job steps for your job.

SQL Server Agent Steps Section

The above screen allows us to organise our different steps(tasks) to execute sequentially.

Now, Click the New button to create a new job step and you will see the New Job Step window as shown below:

SQL Server Agent Step Scripts

Give a Descriptive Name to the step.
Select the Type of program you are going to execute with this step.
Select the database on which the step will act upon.

At last, add your T-SQL Code of your desired action you want to take in command textbox. Click on Parse button to verify if any Syntax error.

You can go to Advanced section and can perform conditional, fail-over action if any as shown below:

Advance SQL Server Agent Step Section

After successfully configuring the steps, click OK to create the step. Repeat this process to add more steps if any to your SQL Server Agent job.

 

4. Schedule the Created SQL Server Agent Job

Now, Let’s schedule the steps defined in above steps to be executed automatically after a certain interval.

Go to Schedules section in the left panel and click on New button to schedule the steps defined above. You will see a New Job Schedule window as shown below:

Schedule SQL Server Agent Job

Give a proper name and you can add different options based on your needs like frequency of this job to be executed, Starting Time, Ending Time etc.

When you are finished click OK to close the Schedule window and OK of New Job Window to create the job. A job with the given name will be added in SQL Server Agent Jobs folder as shown.

Created SQL Server Agent Jobs

 

5. Monitor SQL Server Agent Jobs

Right click on the created job and select View History option to see the logs of the jobs executed with status as shown below:

SQL Server Agent Job History

So, it was all about SQL Server Agents Jobs Creation and Scheduling it, if you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

 

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.