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
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