SQL Server Agent is a component of Microsoft SQL Server that allows for the scheduling and automation of administrative tasks, such as backups, database maintenance, and other routine activities. It is a job scheduling engine that runs scheduled jobs or scripts at specified times and intervals.
SQL Server Agent is designed to be used with SQL Server Management Studio, providing a graphical user interface (GUI) for managing and scheduling jobs and automated tasks Using SQL Server Agent, database administrators can create and schedule jobs that can execute a wide variety of tasks, including Transact-SQL scripts, SSIS packages scripts, and executable programs. The SQL Agent runs as a service in Windows.
SQL Server Agent also includes alerts that can notify administrators of system events and errors, and it logs job history to enable analysis and troubleshooting of issues. In summary, SQL Server Agent provides a centralized tool for automating administrative tasks and managing job execution in SQL Server.
Microsoft SQL Server - SQL Agent Components
There are a few components of the SQL Server Agent service that you must be aware of before proceeding forward.
SQL Server Agent Jobs– This is a program that defines the rules about the repetitive execution of one or more scripts or other utilities within the SQL Server environment
Steps – These can be considered as the building blocks of the jobs. Your job can contain one or multiple steps. Each step executes a specific set of instructions. The next step can be executed based on the success or failure of a previous step
Schedules – These are periodic rules set on the job to execute automatically based on a pre-defined time slot. These jobs can be scheduled hourly, daily, weekly, monthly, or even on specific days of the week
Alerts – SQL Server generates events that are stored in the Microsoft Windows Application Log. Whenever the agent finds a match, it fires an alert which is a response to the event
Notifications – You can set up email notifications to update about the result of the job execution. This is mostly done in case of job failures so that the person responsible for the jobs can take appropriate actions
Where Does The SQL Server Agent Store Job Information
MSDB is a system database in Microsoft SQL Server that is used to store metadata and configuration information for various SQL Server components, including SQL Server Agent. SQL Server Agent uses MSDB to store information about job definitions, job schedules, job history, alerts, and other job-related information.
Finding The SQL Server Agent Service
SQL Server Agent is a background process that runs continuously and performs a variety of tasks such as scheduling jobs, monitoring the system, and executing tasks based on predefined schedules. It provides a centralized location to manage all scheduled tasks, including backup and recovery operations, index rebuilding, and other administrative tasks.
Using SQL Server Management Studio:
Connect to the SQL Server instance that hosts the SQL Server Agent service.
In Object Explorer, expand the "SQL Server Agent" node.
Right-click on the "SQL Server Agent" node and select "Start" or "Stop" as needed.
Microsoft Windows Service
The Agent Can Be started manually or Set To start automatically Start
You can schedule SQL Server Jobs Using SQL Server Agent In A Few Simple Steps. Here's The T SQL Statement Example:
This is the table that we will insert some dates into
CREATE TABLE [dbo].[SQLAgentTest](
[AgentID] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL
) ON [PRIMARY]Insert Into [dbo].[SQLAgentTest] ([Date])
Values (GetDate())Create a new job: Use SQL Server Management Studio to create a new job. To do this, right-click on the SQL Server Agent node in Object Explorer, select "New Job", and enter a name and description for the job.
Name The Job
#1 Click Steps: A job step is a single task that the job will execute. You can add one or more steps to a job, and each step can include a T-SQL script, a command prompt script, or other types of tasks.
To add a new step, click the "New" button in the "Steps" page of the "New Job" dialog.
#2 Add the step name
#3 Add the T-SQL that you are going to execute
Insert Into [dbo].[SQLAgentTest] ([Date])
Values (GetDate())Schedule the job: In the "Schedules" page of the "New Job" dialog, you can define when and how often the job should run. You can choose a predefined schedule or create a custom schedule that meets your needs.
Setup the job to run every 10 seconds
To View Job History
To view the SQL Agent Job history, you can follow these steps:
Expand the "SQL Server Agent" node in the Object Explorer window.
Click on the "Jobs" folder to display a list of all SQL Server Agent jobs.
Right-click on the job for which you want to view the history, and select "View History" from the context menu.
This will open the "Job History" window, which displays a list of all the job executions for the selected job.
You can filter the job history by date, status, and other parameters to quickly locate specific events or job runs.
The job history window also provides detailed information about each job run, including the start time, end time, status, duration, and any error messages that occurred during the job run.
SQL Server Agent Properties:
Expand the "SQL Server Agent" node in the Object Explorer window.
Right-click on the "SQL Server Agent" node, and select "Properties" from the context menu.
This will open the "SQL Server Agent Properties" dialog box, which contains several tabs with different settings.
The "General" tab allows you to configure the default properties for newly created jobs, such as the owner, category, and notification options.
The "Alert System" tab allows you to configure the settings for SQL Server Agent alerts, including the mail profile, database mail settings, and operator notifications.
The "History" tab allows you to configure the retention period for job and alert history, as well as the maximum size of the job and alert logs.
The "Advanced" tab provides additional configuration options, including the maximum number of concurrent job executions, the polling interval for detecting new jobs, and the security settings for SQL Server Agent.
Finally, the "Facets" tab allows you to select specific facets of SQL Server Agent to view and configure, such as jobs, alerts, or operators.
Overall, the SQL Server Agent Properties settings provide a wide range of customization options to help you optimize the behavior of the SQL Server Agent service and improve the reliability and performance of your SQL Server environment.
Additional Resources
Last Notes
If you need help creating job steps or SQL agent jobs. You should connect with me, I can help you manage the MSDB database or define the security context of an executing process. In addition, if you purchase my SQL Server M&M Product I will create jobs to rebuild indexes, detect if there is an SA login and manage and alert if the backup encounters a problem.