GeneralSQL: Querying SQL Server Agent Job Information

https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

Problem

I am a BI developer and have been moved recently to a DBA role. Among the many tasks that I am responsible for, two major ones are: monitoring the SQL Server Agent Jobs running on a set of servers by creating a few standard SSRS reports and creating a Reference Manual/One Stop Guide containing the details of jobs on each of the servers. As I am new to the DBA role, to start with I would like to know some basics of what are the various SQL Server Agent Job related details which need to be reported as part of reports/documentation. Also, is there an easy/automated way to get this information instead of connecting to each server and manually getting the information through the SSMS GUI.

Solution

It is a common scenario in IT the industry where people move from a dev/test role to a DBA role and vice-versa due to various reasons. It is very essential for the person to be able to adapt easily when he/she is moved to a different role. SQL Server is known for its usability and is one of the most liked tools in the community because of its user friendliness. In this tip, we will see how to query some basic job monitoring and job setup information. The intention of this tip is to act as a good starting point for people who are new to the DBA role and for those who need to monitor the SQL Server Agent Jobs.

Before we get into querying the required information, let's get a basic understanding of msdb database. msdb is a SQL Server System Database and is used to store the Configuration, Processing, & Metadata information related to the SQL Server features, including:

SQL Server Agent Jobs, Job Steps, Job schedules, Alerts, Operators, etc.
Service Broker, Log Shipping, database backups and restore information, Maintenance Plan, Database Mail, Policy Based Information of SQL Server 2008, etc.
SQL Server Integration Services (SSIS) packages stored in Integration Services.

In this tip we will retrieve the following information related to SQL Server Agent Jobs by querying some of the system tables in the msdb database:

Job Setup/Configuration Information
Job Execution Information
Job Step(s) Setup/Configuration Information
Job Step(s) Execution Information
Schedule Information

SQL Server Agent Job Setup and Configuration Information

SQL Server allows creation of jobs for performing various tasks in an automated fashion like Replication, running SSIS Packages, Stored Procedures, Batch Commands, etc. These jobs can be created either using SSMS GUI or using T-SQLqueries. Irrespective of the approach used, this information is stored in a set of SQL Server System Tables present in the msdb database.

The following query gives us the Job Level Setup and Configuration information which is also found in the SQL Server Agent Job Properties window in SSMS.

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]