SQL Server agent job is a windows service based scheduler. Agent job is used to schedule and automate SQL scripts, administrative tasks, SSIS packages and various other tasks using SQL agent job tool. This component is integrated along with SSMS and available under Object explorer as SQL Server Agent.
Scheduling tasks like automatic daily backups, index maintenance, backup and recovery, SSIS packages is possible through a SQL agent job. We can create a scheduled job using agent job UI within SSMS or add it using SQL queries with the help of system stored procedures. For example, we can schedule a daily backup job at late night when business is not running. Above all, an agent job can notify you of the success and failure of information through notifications and alerts.
SQL agent job works as a window service, thus in case it’s disabled by default – you may turn it on. In this tech-recipes post, we shall learn to schedule a SQL Agent job using SSMS GUI to schedule a stored procedure, daily, in every 10 minutes interval.
Points to Ponder – SQL Agent Job
1.List of jobs available in “Jobs” folder, under SQL Server Agent in SSMS.
2.Primary selections available with a scheduled job are name, steps, schedule, alerts and notifications.
3.Agent job can schedule an ad-hoc SQL query, SSIS package, stored procedure, PowerShell script and various other objects.
4.msdb – a system database stores all the agent job-related configuration.
5.In case agent job is disabled, try to turn on SQL Server Agent (InstanceName) from services.msc as well as from SSMS object explorer.
Components of SQL Agent Job
1.General – We can specify job name, owner, category and description.
2.Steps – A job may have one or more steps. Each step contains one or more tasks. In case we have to schedule the backup of a database A and database B. We can create multiple steps in a single job to achieve this. Below steps, we can specify Ad-hoc SQL query, stored procedure, or SSIS package name. Moreover, failure scenarios and logging can be done under Steps.
3.Schedules – Following schedules, we can specify the date and time to run a job. Different scheduling options are available as per our requirement. Be it daily, weekly or yearly.
4.Alerts & Notification – Different types of alerts and notifications through email can be sent on completion or failure of Agent job as per our requirement.
Create SQL Agent Job Using SSMS GUI
Quick Guide
SSMS > Object Explorer > Jobs > New Job… > General > Steps > Schedule > OK > Done.
Details Steps
1. In SSMS, navigate to object explorer. Expand SQL server agent node.
2. Select Jobs folder, and right click to create New Job.
3. On New Job page, provide the necessary details. Name of the job, Category and description as per requirement.
4. On the same page, select Steps and choose New.
5. On New Job Page, give a Step Name. Additionally, choose Type/ Database Name and add SQL query under the command and click OK. You can click on Parse to make sure command is syntactically right.
6. On the same page, select Schedule and choose New.
7. On New Job Schedule page, give schedule name and select the appropriate time to schedule a job based on your requirement.
8. You may use advanced options Alert, Notification and Target as per requirement. These options are not needed for basic job setup.
Related Post
How To Schedule SQL Agent Job Using T-SQL Script
Summary
As a result, we have learned how to add SQL Server agent job using SSMS GUI. We have used graphical option and easy steps to add a basic SQL Agent job. If you like this post, you may like to browse through Tech-Tecipes database archives.
