(888) 685-3101 ext. 2

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 = 'test@test.com'; -- 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;
Share This