How to Schedule Jobs in SQL Server using SQL Server Agent

Back to: SQL Server Tutorial For Beginners and Professionals

How to Schedule Jobs in SQL Server using SQL Server Agent

In this article, I am going to discuss How to Schedule Jobs in SQL Server using SQL Server Agent in detail with Examples. Please read our previous article where we discussed Star Schema vs Snow Flake Design in SQL Server. As part of this article, we are going to discuss the following pointers in detail.

1. What is exactly a Job and why we need Job scheduling in SQL Server?
2. What is the SQL Server Agent?
3. How we can create and schedule a Job in SQL Server using SQL Server Agent?
4. How to Monitor the Scheduled Jobs using Job Activity Monitor?

What is the SQL Server Agent?

When you open your SQL Server Management Studio, then you will find a small icon called SQL Server Agent as shown in the below image.

The SQL Server Agent is nothing but a feature provided by SQL Server which will help us to run a job (task) after a specific time interval. For example, if you want to run a backup process every night or if you want to execute a task after a specific time interval, then in such scenarios the SQL Server Agent comes into the picture.

How to use SQL Server Agent to Schedule a Job?

As you can see, by default the SQL Server Agent is disabled. So, first, we need to enable SQL Server Agent. To do so, right-click on the SQL Server Agent and click on the Start option which will prompt a popup asking for do you want to start the SQL Server Agent and you simply need to click on the Yes option as shown in the below image.

Once you click on the Yes option, then it will take some time to start the SQL Server Agent service. Once the SQL Server Agent is started, just refresh the SQL Server Agent and expand it, you will find the following things within the SQL Server Agent.

What is Job?

A job can have a series of steps or a series of logic that you want to execute one after another. So, in SQL Server Agent, you can create Jobs and inside each job, you can define one or more steps, and all these steps you can run one after another. For better understanding please have a look at the following diagram.

Understanding How to Schedule Job in SQL Server with an example:

We are going to work with the following ShoppingCart database and Orders and OrdersHistory tables. So, please use below SQL Scripts to create the required database and tables.

CREATE DATABASE ShoppingCart;
GO

USE ShoppingCart;
GO

CREATE TABLE Orders
(
  OrderId INT,
  CustomerId INT,
  Amount INT,
  OrderDate DATETIME
)
GO

CREATE TABLE OrdersHistory
(
  OrderId INT,
  CustomerId INT,
  Amount INT,
  OrderDate DATETIME
)
GO

Next, we need to insert some data into the Orders table. So, please use the following script to insert some dummy data into the Orders table.

INSERT INTO Orders VALUES (101, 100001, 10000, GETDATE())
INSERT INTO Orders VALUES (102, 100003, 10000, GETDATE())
INSERT INTO Orders VALUES (103, 100001, 30000, GETDATE())
INSERT INTO Orders VALUES (104, 100005, 50000, GETDATE())
INSERT INTO Orders VALUES (105, 100003, 70000, GETDATE())

Business Requirements:

Our business requirement is to create a job that will run every 30 minutes. In that job first, it will insert all the records which are present in the Orders table into the OrdersHistory table, and then it will delete all the records from the Orders table.

Step1: Creating Job using SQL Server Agent

Right-click on the Jobs folder and select the new job option from the context menu as shown in the below image.

Once you click on the New Job option, it will open the Job window. From the Job window, select the General tab and provide the following detail. Provide a meaningful name to your job. Here, I am providing the name as BackupAndDeleteInEvery30Minutes.

In the Owner section, you need to specify the user account on which this job is going to run. The user should have all the rights to fire the SQL statements. So, here I am selecting the NT AUTHORITY\SYSTEM account by clicking on the browse option which appears next to the Owner text box. 

In the category section, you need to specify the type of Job. And here the job type is Database Maintenance. Here, I am selecting the Database Maintenance from the Category dropdown list. In the Description textbox, provide a description of your job as shown in the below image.

Step2: Creating Job Steps

Once you create the job, next you need to define the steps as we already discussed a job is nothing but a series of steps that are going to be executed one after another. In order to create a step, first, select the Steps option from the left pane and then click on the New button as shown in the below image.

Once you click on the New button, it will open the new JOB step window. From this window provide the following details.

First, you need to provide a meaningful name to your job step. Here, I provided the name Insert Data Into OrdersHistory from Orders table. Then you need to select the type of job. Here, I am selecting the Job type as T-SQL as I am going to write a T-SQL Statement as part of this Job Step.

Then you need to select the database. As, my required tables are within the ShoppingCart database, so here, I am selecting the database as ShoppingCart. Finally, you need to write the required T-SQL statement in the Command Text box. Here, my T-SQL statement is INSERT INTO OrdersHistory SELECT * FROM Orders which is insert all the records into the OrderHistory table which are present in the Orders table. Then I click on the Ok button which will create job step 1 as shown in the below image.

Once you click on the Ok button, then it will create job step 1 successfully. Then we need to create Job Step 2 which is basically used to delete all the records from the Orders table. To do so, again click on the New button and provide the details as shown in the below image.

Here, we specify the command as DELETE FROM Orders. And once you click on the OK button, it should create Job Step 2 and you can see both job steps in the job step lists as shown below.

In this way, you can define a series of steps for a particular job in SQL Server and these steps are going to be executed one after another in the Step sequence. That means first step 1, then step 2, and so on. If you want then you can also change the job step sequence by using the move step buttons.

Step3: Scheduling the Job in SQL Server Agent

Once you created the job and the job steps, then the next thing that you need to do is, you need to define the schedule or time interval for this job to run. To do so, click on the Schedule option from the left pane which should open the window to create the schedule. From this scheduled window, just click on the new button as shown in the below image.

Once you click on the New Button, it will open the window where you need to provide the required information to schedule this job. First, you need to provide a meaningful name to your schedule. Here, I am providing the name as Every30Miniutes in the Name text box.

As we want to run this job daily, so here we need to set the Occurs dropdown value as Daily. Then select the occurs every radio button and provide the Occurs Every value as 30 and then select the Minutes radio button and then provide the start time i.e. when the job is going to execute for the first time. Currently, in my machine, it is 15:45 PM, so I am giving the start time as 15:50 and then click on the Ok button as shown below.

Once you click on the OK button, it will create the schedule as shown below. Now, finally, click on the Ok button which will run the job in every 30 minutes time interval.

Once you click on the OK button, then you can see this job in the Jobs folder as shown below.

Job Activity Monitor in SQL Server Agent:

If you want to check whether the job is run successfully or whether you got any error, then you need to use the Job Activity Monitor which you can find in SQL Server Agent. To use this, simply, right-click on the Job Activity Monitor and select View Job Activity from the context menu as shown below.

Once you select the View Job Activity option, then it will open the below window.

As you can see, the status is Idle means the job is not yet run. We need to wait the time we specified. Once the job is run, we will get the following in the View Job Activity. Please click on the refresh option to check the latest status.

If you get any error, then please check the user credentials which is used to run the job. If you want to see the job history, then simply right-click on the Job in View Job Activity window and click on the View history option as shown below.

Once you click on the View history option, it will open the below window which shows the history of this job.

In the next article, I am going to discuss How SQL Server Store and Manages the Data Internally in detail. In this article, I try to explain How to Schedule Jobs in SQL Server using SQL Server Agent with Examples. I hope you enjoy this article How to Schedule Jobs in SQL Server using SQL Server Agent with Examples article and understand How to Schedule Jobs in SQL Server using SQL Server Agent.