Job Summary

Download the .zip file that includes scripts and an SSRS report displaying a history of jobs within date parameters and all their most important execution details.

SCRIPT: Job Summary

/****************The Lytic Grouyp, copyright, 2021*****************/

/****************Published March 17, 2021 *****************/

/*********from by Edward Heraux*****************/


Run this script all the way through to create a user-defined function (ufnServerInfo), 

and a stored procedure (uspJobsSummary) to feed the report included with this script 

in the .zip file you downloaded (JobsSummary.rdl or JobsSummary2005/2008).



Return some basic information about your SQL Server 

and the edition & service pack you're running.

Used in the header of the included reports.




IF OBJECT_ID (N'dbo.ufnServerInfo', N'IF') IS NOT NULL

    DROP FUNCTION dbo.ufnServerInfo


CREATE FUNCTION dbo.ufnServerInfo()




--Grab server name and version info

SELECT SERVERPROPERTY('servername') AS ServerName

	, @@Version as SQLServerVersion

	, CONVERT(VARCHAR(100), serverproperty('Edition')) AS Edition

	, SERVERPROPERTY('productversion')AS Version

	, SERVERPROPERTY ('productlevel') AS ProductLevel

	,CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType')) AS LicenseType



Pass any 2 date parameters to this stored procedure and it will produce a list of 

all essential info about all jobs, on all your databases, that ran in between those dates.


IF OBJECT_ID (N'dbo.uspJobSummary', N'IF') IS NOT NULL

    DROP PROC dbo.uspJobSummary


CREATE PROC [dbo].[uspJobSummary] @StartDate datetime=NULL

							, @EndDate datetime = NULL --getdate



--Setting the default @StartDate to 1/1/1900

SET @StartDate=ISNULL(@StartDate,'19000101 12:00:00 AM' )

--Setting the default @EndDate to the end of the day, either on the date passed or today if there was none

SET @EndDate= CAST(CAST(@EndDate  AS char(12))+ ' 23:59:50.999' AS datetime)

SET @EndDate=ISNULL(@EndDate,CAST(CAST(getdate() AS char(12)) + ' 23:59:59.999' AS datetime))


	--Create a table variable  to hold the data generated by sp_help_jobhistory

declare @JobHistory table(

    instance_id int null, 

    job_id uniqueidentifier null, 

    job_name sysname null, 

    step_id int null ,

    step_name sysname null, 

    sql_message_id int null, 

    sql_severity int null, 

    message nvarchar(4000) null, 

    run_status int null, 

    run_date int null, 

    run_time int null, 

    run_duration int null, 

    operator_emailed sysname null, 

    operator_netsent sysname null, 

    operator_paged sysname null, 

    retries_attempted int null, 

    server sysname null,

     run_duration_formatted  AS RIGHT('000000' + CONVERT(varchar(6), run_duration), 6),

     run_time_formatted AS RIGHT('000000' + CONVERT(varchar(6), run_time), 6), 

     run_date_formatted AS LEFT(CAST(run_date AS varchar(10)),4) + '-'

				+ SUBSTRING(CAST(run_date AS varchar(10)),5,2) + '-'

				+ RIGHT(CAST(run_date AS varchar(10)),2) 


--Populate the table variable with job history data

insert into @JobHistory 

exec msdb.dbo.sp_help_jobhistory @mode='Full'		

--Show us the job history within the passed parameters		

SELECT H.job_name AS JobName, S.database_name  AS DatabaseName 		

		, CASE H.run_status      

			WHEN 0 THEN 'Failed'

			WHEN 1 THEN 'Success'  

			WHEN 3 THEN 'Cancelled'  

			WHEN 5 THEN 'Unknown'  

			ELSE 'Other'  

		END AS RunStatus

		,LEFT(H.run_duration_formatted,2) + ':' + SUBSTRING(H.run_duration_formatted,3,2)+':' + Right(H.run_duration_formatted,2)  AS RunDuration

		,H.run_date_formatted AS RunDate, H.run_time_formatted AS RunTime

		--concatenate two of the computed columns from the Table variable into a proper run date & time

		,CONVERT(datetime,H.run_date_formatted + ' ' + LEFT(H.run_time_formatted,2) + ':' + SUBSTRING(H.run_time_formatted,3,2)+':' + RIGHT(H.run_time_formatted,2)) AS RunDateTime

FROM @JobHistory H

		INNER JOIN (SELECT job_id, MAX(database_name) AS database_Name

					FROM msdb.dbo.sysjobsteps 

					GROUP BY job_id) S

			ON H.job_id=S.job_id		

WHERE H.step_id=0 	

	AND CONVERT(datetime,H.run_date_formatted + ' ' + LEFT(H.run_time_formatted,2) + ':' + SUBSTRING(H.run_time_formatted,3,2)+':' + RIGHT(H.run_time_formatted,2)) 

			BETWEEN @StartDate and CAST(@EndDate AS char(12)) + ' 23:59:59.999'

ORDER BY H.run_date_formatted desc



		--We're going to refer to these variables in a RAISERROR command

		DECLARE @ErrorMessage NVARCHAR(4000)

		DECLARE @ErrorSeverity INT

		DECLARE @ErrorState INT

		SELECT @ErrorMessage = 'Error number ' + CAST(ERROR_NUMBER() AS varchar(10)) +': ' + ERROR_MESSAGE(),

			  @ErrorSeverity = ERROR_SEVERITY(),

			  @ErrorState = ERROR_STATE()