SQL Job Status Reporting

One day I suddenly see that my LiteSpeed DB backup job is running and running forever ! - though it did not fail but it did not complete so its still a failure in my opinion. This can happen due to third part component failure, or due to bug in the Windows OS or in the SQL Server agent. So, for what so ever reason if job/step is hung, its an issue. To overcome this issue I decided to email out the SQL Job status as a summarised report to myself (and other as required).

Here is the script to achieve the same. It will send out an email (assuming you have configure email feature on your SQL Server) with SQL Job status like LastRun time LastRunStatus and TimeTaken for latest execution.

--===================================================

DECLARE @tableHTML NVARCHAR(MAX)

,@SubjLine varchar(500)

SET @tableHTML =

N'<H4>SQL Server Job Status Report:</H4>' +

N'<table border="1">' +

N'<tr><th>Job Name</th><th>is enable ?</th>' +

N'<th>LastRunDateTime</th><th>LastRunDuration</th><th>LastRunOutCome</th>' +

CAST ( (

SELECT

td = J.Name, '',

td = J.Enabled, '',

td = left(Jh.run_date,4) + '-' + SUBSTRING(CAST(Jh.run_date AS CHAR),5,2) + '-' + right(Jh.run_date,2) +' '+

LEFT(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),5,2), '',

td = LEFT(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_duration)) + CAST(Jh.run_duration AS CHAR(6)),5,2), '',

td = CASE run_status

when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'Progress'

end, ''

FROM msdb.dbo.sysjobhistory Jh

JOIN msdb.dbo.sysjobs J ON Jh.job_id = J.job_id

WHERE

Jh.Instance_id IN (

SELECT MAX(Jh.instance_id) [Instance_Id]--, J.Job_Id INTO #JobHist

FROM msdb.dbo.sysjobhistory Jh

JOIN msdb.dbo.sysjobs J ON Jh.job_id = J.job_id

WHERE

--Jh.step_id = 0 -- I don't step details, just job outcome

--AND J.enabled = 1 -- enable this condition to filter out DISABLED jobs

J.Name IN (SELECT JobName FROM Monitoring.dbo.SQLJobsToMonitor WHERE ReportedYesNo = 1)

GROUP BY J.Job_Id

)

ORDER BY

CONVERT(DATETIME,

left(Jh.run_date,4) + '-' + SUBSTRING(CAST(Jh.run_date AS CHAR),5,2)

+ '-' + right(Jh.run_date,2) +' '+

LEFT(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),3,2) + ':' +

SUBSTRING(REPLICATE('0', 6-LEN(Jh.run_time)) + CAST(Jh.run_time AS CHAR(6)),5,2)

)

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table><br><h5>Job name: Monitoring - SQL Job Status Report<h5>' ;

SET @SubjLine = 'SQLMail Monitor: '+@@ServerName+': Job Status Report'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLMail_Mail',

@recipients = 'emailid_001;Emailid02',

@subject = @SubjLine,

@body = @tableHTML,

@body_format = 'HTML';

--===================================================

"Monitoring.dbo.SQLJobsToMonitor" is a table which will be holding the list of jobs to be reported, you can add or delete or set the flag "ReportedYesNo" column to 0/1 to filter out the less important jobs from being reported.

Here is the table structure for the same.

--===================================================

CREATE TABLE SQLJobsToMonitor (

PkId INTEGER IDENTITY PRIMARY KEY NOT NULL,

JobName varchar(500),

ReportedYesNo bit default 1

)

--===================================================

Report Sample:

SQL Server Job Status Report:

Job Name	is enable ?	LastRunDateTime	LastRunDuration	LastRunOutCome
SS Maint Job - exec Prc_FutureRevenue @Thread = 1	0	2009-03-14 01:15:17	01:56:31	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 0	0	2009-03-14 01:15:17	01:56:53	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 4	0	2009-03-14 01:15:17	01:57:10	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 9	0	2009-03-14 01:15:17	01:58:15	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 6	0	2009-03-14 01:15:17	01:58:25	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 3	0	2009-03-14 01:15:17	01:58:38	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 2	0	2009-03-14 01:15:17	01:59:18	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 5	0	2009-03-14 01:15:17	01:59:36	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 7	0	2009-03-14 01:15:17	03:22:27	Succeeded
SS Maint Job - exec Prc_FutureRevenue @Thread = 8	0	2009-03-14 01:15:17	03:23:24	Succeeded