Search This Blog

SQL Server Job-Creating and Scheduling a SQL Job with an example

SQL Server Job-Creating and Scheduling a SQL Job with an example

In my previous post I have showed how to execute a sql script using a batch file. This post is about executing and automating  sql script using SQL Jobs
.
First we will create a test table and we will truncate the table using the SQL Job.

We can schedule a SQL  job to run at specific point or to run recursively after some time frame or we can schedule it daily, weekly, Monthly  quarterly and so on.

Below is an example :-

First we will create a database and a table and we will truncate that table using SQL  Job
--Below is the table and test script

create database test

create table test
(id int )

insert into test values (1),(2),(3)

Now we will truncate the table using SQL Job and will automate it to run on daily basis. For that we will write the truncate query as per the requirement. Here I will show an example of truncating table by scheduling a job.

We can consider a scenario where we need to truncate the table automatically on daily basis at 8 P.M so we will create a Job that will run on daily basis and will truncate the data daily daily at 8 p.m.

First Step

In Object Explorer Go to SQL Server Agent-Jobs
Note : Make Sure SQL Server Agent is running on your instance otherwise you won’t be able to see Jobs.
Right click on Job
New Job as in below screenshot.


Second Step
Give a Name to a Job and add a description as below Screenshot


Third Step
Go to Steps add a new step as below screenshot
-Give Step a Name
-Type-T-SQL  (As we are directly running a sql script in the job)
-Database-Select the database in which we are truncating table
-Command-Write or paste your SQL Code as in below screenshot.

Truncate table test



Step 4-Schedule the Job

-Click on New Schedule
-Give Job Schedule a Name
-Schedule Type-Recurring as we want to truncate the table daily at 8.PM
-Recurs every-1 Day
-Occur Once at -8 P.M as in below screenshot.



Schedule Type Description as below. 

Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started. 

Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition. 

Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog. 

Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog

Now you are done with your Job If you want any Alerts if Job fails and Notification to be send after completion of Job then you can add those steps also.

Target- If in case you want to target the Job in more than once server you can configure that also

A Target server allows the job to be run against a different server than the one it is defined on. 

Now once done click ok

In order to verify Job Right click on Job and run job you will get success message as below.

Now after testing schedule your job as per requirement

NOTE: SQL Jobs are not only limited to SQL Scripts you can create jobs to run SSIS Packages, analysis Services queries, Power shell commands and many others.














No comments:

Post a Comment