SQL Server Job Reporting – SQL Toolbox

SQL Server is indeed a powerfull database management system, however Microsoft has not yet included the run yourself feature as of yet. And it is because of this Database Administrators need a good set of tools to not only manage SQL Server but to also monitor what is going on in the background.

Todays new tool for the SQL Tool Box, Automated Job Failure Reports

As opposed to going to every server and checkign every jobs history this script, using proeprly configured database mail, will send an email to the intended recipients of the jobs that have failed in the last 24 hours. Simply add this script to a new job, set the configuration values in the script, set a schedule for the desired time of delivery and you are set!

 

set nocount on; 

/* declare modifiable variables */

declare @recipients varchar(1000); -- holds the primary report recipient email addresses

declare @subject varchar(256); -- stores the subject of the email

declare @profile_name varchar(200); -- the SQL Mail profile name

declare @cc_recipients varchar(1000); -- holds the primary report recipient email addresses

/* set values for modifiable variables */

set @recipients = '[email protected]'; -- primary recipients of report

set @subject = 'Failed Job Report From';

set @profile_name = 'TEST'; -- SQL Mail Profile to be used

set @cc_recipients = ''; -- alternate reciepients of report

/* Script required variables */

declare @msgHeader varchar(max); -- used to store message body header

declare @msgBody varchar(max); -- used to store message body

declare @msgFooter varchar(max); -- used to store message body footer

declare @body varchar(max); -- used to store entire body of the message for the email

declare @tblRow varchar(max); -- used to temporarily store reporting table row

declare @id int; -- temporary place holder for job table pk id

-- temp table to hold collected job data 

declare @jobtable table

(

id int not null identity, 

job_id varchar(255),

instance_id int, 

step_id int,

sql_message_id int,

err_message varchar(max),

job_name varchar(255), 

step_name varchar(255), 

command varchar(max) null,

run_timestamp datetime

);

/* inserts selected data into temp table @jobtable */

insert into @jobtable 

select jh.job_id,

jh.instance_id, 

jh.step_id, 

jh.sql_message_id, 

jh.[message],

sj.name, 

jh.step_name, 

(select command from msdb.dbo.sysjobsteps where job_id = jh.job_id and step_id = jh.step_id), 

(SELECT CONVERT(datetime, (SELECT convert(varchar, convert(datetime, convert(varchar, jh.run_date)) , 111) + ' ' + substring(convert(varchar, jh.run_time), 1, 2)+ ':' + substring(convert(varchar, jh.run_time), 3, 2) + ':' + substring(convert(varchar, jh.run_time), 5, 2)), 111)) as dt

from msdb.dbo.sysjobhistory jh

inner join msdb.dbo.sysjobs sj 

on jh.job_id = sj.job_id 

where jh.run_status <> 1

and jh.step_id <> 0

and datediff(hour, (SELECT CONVERT(datetime, (SELECT convert(varchar, convert(datetime, convert(varchar, jh.run_date)) , 111) + ' ' + substring(convert(varchar, jh.run_time), 1, 2)+ ':' + substring(convert(varchar, jh.run_time), 3, 2) + ':' + substring(convert(varchar, jh.run_time), 5, 2)), 111)), CURRENT_TIMESTAMP) < 24

-- create the message body header

set @msgHeader = 

'<h3><font color="Red">Failed Job Report</h3>' + 

'<h3>' + @@SERVERNAME + '</h3>' +

'<table id="box-table" border=''1''>' +

'<tr>' + 

'<td>Job Name</td>' +

'<td>Step Name</td>' +

'<td>Command</td>' +

'<td>Error Message</td>' +

'<td>Date / Time</td>' +

'</tr>';

set @msgBody = ''; -- for some reason we have to intialize the string as blank

-- use cursor to iterate through all rows of @jobtable

declare db_cursor cursor for

select id

from @jobtable

open db_cursor

fetch next from db_cursor into @id

while @@FETCH_STATUS = 0

begin

-- create a row of text for the report

select @tblRow = '<tr>' +

'<td>' + isnull(ltrim(rtrim(job_name)),'..') + '</td>' + 

'<td>' + isnull(ltrim(rtrim(step_name)),'..') + '</td>' +

'<td>' + isnull(ltrim(rtrim(command)),'..') + '</td>' +

'<td>' + isnull(ltrim(rtrim(err_message)),'..')+ '</td>' +

'<td>' + isnull(cast(run_timestamp as varchar(255)),'..') + '</td>' +

'</tr>'

from @jobtable where id = @id;

-- Add text to the report content

set @msgBody = @msgBody + @tblRow;

-- grab the next available record

fetch next from db_cursor into @id

end

close db_cursor

deallocate db_cursor

-- set the message body footer

set @msgFooter = '</table>';

-- set value for finaly @body variable to be sent in email

set @body = @msgHeader + @msgBody + @msgFooter;

-- add teh server name to the subject line 

set @subject = @subject + ' '+ @@SERVERNAME

-- send the email

exec msdb.dbo.sp_send_dbmail

@recipients= @recipients,

    @copy_recipients =@cc_recipients,

    @profile_name = @profile_name,

    @importance='High',

@subject =@subject,

@body_format = 'HTML',

@body= @body;