Enterprise SQL Job Activity Reporting

As DBAs, we often find ourselves spending a large portion of our day reviewing SQL Server job activity, and if you happen to have many SQL Servers with many jobs running on them and you don't happen to have an enterprise SQL job assessment tool like SQL Sentry, then you're probably accessing each server one at a time via Management Studio to view job statuses. Well, I recently grew very tired of this mundane routine and I decided it was time to automate the process using tools within the SQL Server arsenal.

This article illustrates a solution that will gather SQL job activity from all your SQL 2000, 2005 and 2008 servers and will email you a report according to a predetermined schedule.

Figure 1 - SQL Job Activity Report Example

The components of this solution are a SQL Server Integration Services (SSIS) package, a SQL script and a SQL Server Reporting Services (SSRS) report.

NOTE: The SQL script used in this solution was derived from Vidhya Sagar's blog at:
http://sql-articles.com/blogs/script-to-find-sql-job-activity-details/. I tweaked Vidhya's script by removing the use of a temporary table, by adding the current_execution_status column to the SELECT statements, and by adding several formatting statements as to ensure the script would run within the SSIS construct.

STEP 1: Designate Host SQL Server / Create JobDetails Table

First, you must designate a SQL Server as a host, i.e. the server on which all SQL job activity will be gathered. Personally, I use a local instance SQL Server on my PC as the host.

NOTE: SQL Server Express edition will not work as a host, as SSIS and SQL Agent will need to be running.

Next, create a "JobDetails" table in a designated database. Run the following script to create the table:

CREATE TABLE [dbo].[JobDetails](
 [server_name] [varchar](200) NOT NULL,
 [job_name] [varchar](500) NOT NULL,
 [job_status] [varchar](8) NULL,
 [current_execution_status] [varchar](30) NULL,
 [last_run_status] [varchar](15) NULL,
 [last_run_date] [datetime] NULL,
 [run_duration] [varchar](50) NULL,
 [next_scheduled_run_date] [datetime] NULL,
 [step_description] [varchar](500) NULL
 CONSTRAINT [PK_JobDetails] PRIMARY KEY CLUSTERED 
 ([server_name] ASC,[job_name] ASC)) ON [PRIMARY];
GO

STEP 2: Create SQL Script File

Create a .sql script file to select the job activity data from each server. This script will later be imported into the SSIS package. The contents of the file should be as follows:

/*####################### INITIALIZATION SECTION ############################*/

SET NOCOUNT ON;
SET FMTONLY OFF;
DECLARE @ServerName VARCHAR(200),
 @Version TINYINT; 
SELECT @ServerName = CONVERT(VARCHAR(200),SERVERPROPERTY('ServerName')),
 @Version = CONVERT(TINYINT,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1)));

/*############################ MAIN SECTION #################################*/

IF @Version > 8
 BEGIN
 --Collect job activity from SQL 2005 and 2008 servers.
 SELECT @ServerName AS server_name,
 CONVERT(varchar(500),j.name) AS job_name,
 CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
 CASE jsp.current_execution_status 
 WHEN 1 THEN 'Executing'
 WHEN 2 THEN 'Waiting for thread'
 WHEN 3 THEN 'Between retries'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Suspended' 
 WHEN 7 THEN 'Performing completion actions'
 ELSE 'Status unknown'
 END AS current_execution_status,
 CASE jh.run_status 
 WHEN 0 THEN 'Error failed'
 WHEN 1 THEN 'Succeeded'
 WHEN 2 THEN 'Retry'
 WHEN 3 THEN 'Cancelled'
 WHEN 4 THEN 'In progress' 
 ELSE 'Status unknown'
 END AS last_run_status,
 ja.run_requested_date as last_run_date,
 CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
 ja.next_scheduled_run_date,
 CONVERT(VARCHAR(500),jh.message) AS step_description
 FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes', 'set fmtonly off exec msdb.dbo.sp_help_job') jsp
 JOIN msdb.dbo.sysjobactivity ja ON jsp.job_id = ja.job_id
 LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
 JOIN msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
 WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) ORDER BY job_name,job_status;
 END
ELSE
 BEGIN
 --Collect job activity from SQL 2000 servers.
 SELECT @ServerName AS server_name,
 CONVERT(varchar(500),jd.job_name) AS job_name,
 jd.job_status,
 jd.current_execution_status,
 jd.last_run_status,
 CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,
 CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
 CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,
 CONVERT(VARCHAR(500),jh.message) AS step_description
 FROM ((
 SELECT job_id,
 CONVERT(VARCHAR(500),[name]) AS job_name,
 CASE enabled WHEN 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
 CASE current_execution_status 
 WHEN 1 THEN 'Executing'
 WHEN 2 THEN 'Waiting for thread'
 WHEN 3 THEN 'Between retries'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Suspended' 
 WHEN 7 THEN 'Performing completion actions'
 ELSE 'Status unknown'
 END AS current_execution_status,
 CASE last_run_outcome 
 WHEN 0 THEN 'Error failed'
 WHEN 1 THEN 'Succeeded'
 WHEN 2 THEN 'Retry'
 WHEN 3 THEN 'Cancelled'
 WHEN 4 THEN 'In progress' 
 ELSE 'Status unknown' 
 END AS last_run_status,
 CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,
 CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,
 CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,
 CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,
 last_run_date AS lrd, 
 last_run_time AS lrt
 FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes', 'set fmtonly off exec msdb.dbo.sp_help_job')
 ) jd 
 LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id = jh.job_id AND jd.lrd = jh.run_date AND jd.lrt = jh.run_time)
 WHERE step_id = 0 OR step_id IS NULL
 ORDER BY jd.job_name, jd.job_status; 
 END
/*################################ END ######################################*/

NOTE: On SQL 2005 and 2008 servers, the "Ad Hoc Distributed Queries" advanced property must be enabled in order for this script to execute successfully.

STEP 3: Create / Deploy SSIS Package to Host Server

Using SQL Server Business Intelligence Development Studio, create a SSIS package that will connect to all the SQL Servers for which you wish to gather SQL job activity as follows.

1. Create an OLE DB connection manager to each source SQL Server's msdb database and to the destination host server (to the database in which the JobDetails table was created in step 1).
2. Create an "Execute SQL Task" control flow task that connects to the destination host and uses a direct input SQL statement of "TRUNCATE TABLE JobDetails;". This step truncates the JobDetails table in order to prepare for the collection of new job activity.

Figure 2 - Execute SQL Task Editor

3. Create a "Data Flow" control flow task that will collect the job activity. NOTE: If you connect to all your SQL Servers via a Windows account that has System Administrator access, you may opt to place the Data Flow task inside a For Each Loop Container and then connect to each server by dynamically updating the connection string using expressions. In my case, I have several SQL Servers in a DMZ, so I must connect using SQL authentication and therefore I cannot use a For Each Loop Container.

Figure 3 - SSIS Package Control Flow

4. Add "OLE DB Source" data flow tasks to collect the job activity (one for each SQL Server source). The Source tasks should use the SQL script created in step 2.
5. Add "OLD DB Destination" data flow tasks to insert the job activity into the JobDetails table on the host (one for each Source task).
6. Connect each Source task to each Destination task. Ensure the mapping is correct.

Figure 4 - SSIS Package Data Flow

7. OPTIONAL: Add a package level error handler to email you any SSIS package errors.
8. Test the SSIS package. Take note on how long it takes to execute (this will vary depending on how many servers on which you are collecting job activity).
9. Build and deploy the SSIS package to the designated host SQL Server.
10. Create a SQL job on the host SQL Server to execute the SSIS package. Schedule the job according to your needs. Personally, I like to have my SSIS package run twice per day -- 7:00am and 4:00pm.

STEP 4: Create SSRS Report

Using SQL Server Business Intelligence Development Studio, create a SSRS report to display the contents of the JobDetails table on the host server as follows:

1. Create a data source to connect to the host SQL Server (I recommend using SQL authentication for this; it's far less buggy). The data source may optionally be set up as shared.
2. Create a data set using a text command type with the value of "SELECT * FROM JobDetails;".
3. Add a table to the report and create a column for each field in the query results.
4. Add the fields and column headers from the data set to their respective table column.
5. Set up grouping on the "server_name" field.
6. OPTIONAL: Add the expression "=iif(Fields!last_run_status.Value="Error failed","Red",Nothing)" to the Background Color property and the expression "=iif(Fields!last_run_status.Value="Error failed","Yellow",Nothing)" to the Font Color property of all the fields (in the detail row) to highlight failed jobs according to their last run status.

Figure 5 - Failed Job Report Highlighting

7. Test the report.
8. Build and deploy the report to an instance of SQL Server running SSRS.
9. Access the SSRS instance where the report was deployed and create a subscription to email the report to you on schedule (after each run of the SSIS package -- allowing ample time for the package to finish).

And there you have it -- no more accessing each SQL Server individually to assess job statuses!