SQL Server Agent

SQL Server Agent

It is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs.

SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database.

SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you.

And all its data saved in msdb database.

SQL Server Components

SQL Server Agent uses the following components to define the tasks to be performed, when to perform the tasks, and how to report the success or failure of the tasks.

1. Jobs

A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers.

2. Schedules

A schedule specifies when a job runs. More than one job can run on the same schedule, and more than one schedule can apply to the same job.
A schedule can define the following conditions for the time when a job runs:

• Whenever SQL Server Agent starts.
• Whenever CPU utilization of the computer is at a level you have defined as idle.
• One time, at a specific date and time.
• On a recurring schedule. 

3. Alerts
An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.

• 
  An alert can respond to one of the following conditions:
• SQL Server events
• SQL Server performance conditions
• Microsoft Windows Management Instrumentation (WMI) events on the computer where SQL Server Agent is running

An alert can perform the following actions:

• Notify one or more operators
• Run a job

4. Operators

An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. In some enterprises, operator responsibilities are assigned to one individual. In enterprises with multiple servers, many individuals can share operator responsibilities. An operator does not contain security information, and does not define a security principal.

SQL Server can notify operators of alerts through one or more of the following:

• E-mail
• Pager (through e-mail)
• net send

5. SQL Error logs

SQL Server Agent creates an error log that records warnings and errors by default. The following warnings and errors are displayed in the log:

• Warning messages that provide information about potential problems, such as "Job <job_name> was deleted while it was running."
• Error messages that usually require intervention by a system administrator, such as "Unable to start mail session." Error messages can be sent to a specific user or computer by net send.

SQL Server maintains up to nine SQL Server Agent error logs. Each archived log has an extension that indicates the relative age of the log. For example, an extension of .1 indicates the newest archived error log and an extension of .9 indicates the oldest archived error log.

Security for SQL Server Agent

SQL Server Agent uses the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in the msdb database to control access to SQL Server Agent for users who are not members of the sysadmin fixed server role. In addition to these fixed database roles, subsystems and proxies help database administrators ensure that each job step runs with the minimum permissions required to perform its task.

Roles

Members of the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole fixed database roles in msdb, and members of the sysadmin fixed server role have access to SQL Server Agent. A user that does not belong to any of these roles cannot use SQL Server Agent. For more information on the roles used by SQL Server Agent. 

To start, stop, or restart the SQL Server Agent Service

1. In Object Explorer, click the plus sign to expand the server where you want to manage SQL Server Agent Service.
2. Right-click SQL Server Agent, and then select either Start, Stop, or Restart.
3. In the User Account Control dialog box, click Yes.
4. When prompted if you want to perform the action, click Yes.

   

   SQL Server Agent