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.
- 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.
- 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.
- 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
- 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.
- SQL Server Agent should be running. If not, open the SQL Server Configuration Manager and start the service.
- 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:
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:
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.
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:
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:
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:
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.
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:
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.
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.