SQL SERVER AGENT

What is a SQL Server job?

A job is a specified series of actions that SQL Server Agent performs. We can use jobs to define an administrative task that can be run one or more times and monitored for success or failure. According to one or more schedules. • In response to one or more alerts. • By executing the sp_start_job stored procedure. consist of running a Transact-SQL statement, executing an SSIS package

What is a Schedule?

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

What is an Alert?

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 •

What is an Operator?

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.

Which tables in msdb system database can be Queried to get details about the Jobs and its steps?

Below are some examples of the tables in msdb database which contains information about the Jobs and its steps. • sysjobs Stores the information for each scheduled job to be executed by SQL Server Agent. • sysjobsteps Contains the information for each step in a job to be executed by SQL Server Agent.

How do you troubleshoot errors in a SQL Server Agent Job?

Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop-down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.

What is the function of SQL Server Agent Windows service?

It is a Windows service which handles tasks scheduled within the SQL Server environment. These tasks are also called jobs and are stored with in SQL server. The jobs may run through a trigger, a predefined schedule or on demand. • This service is very useful in determining why a particular job did not run as intended.

Which database stores the SQL Server Agent objects?

MSDB ▪ Additional information - Accessing SQL Server Agent Data

What is SQL Server Agent? What are its benefits?

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, TSQL Statements, SSIS packages, Reports subscriptions etc. SQL Server agent plays a key role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management.

How can we grant permissions on the SQL Server agent jobs and which roles are available?

SQL Server contains 3 fixed database roles on the MSDB database, which gives administrators fine control over access to SQL Server Agent. The SQL Server Agent node in SSMS is visible only to users in one of these 3 roles (except sysadmins, who can see everything irrespective of role membership). Here is an explanation of the roles, in order from the most restrictive to least restrictive: SQLAgentUserRole - Users in this role are granted view/edit/delete/execute access to only jobs owned by them. SQLAgentReaderRole - Users in this role get all the privileges of theSQLAgentUserRole, i.e. they get access to owned jobs. In addition to that, they can also view (but not modify or execute) all jobs on SQL Server Agent, irrespective of ownership.

True or False - Can a single Job have multiple Job Schedules?

True.

Which tables will give you the Job history related information?

dbo.sysjobhistory

Which table contains information about the SQL Server jobs schedule?

sysjobschedules It contains schedule information for jobs to be executed by SQL Server Agent

Which database contains information about the jobs and other components which are required for the execution of scheduled jobs by SQL Server Agent?

system database- msdb

What are various components of SQL Server Agent service?

• Jobs • Schedules • Operators • Alerts