SQLSAT13 - Job Failure Email

This Stored Procedure is the main one I can't live without. Every morning I get an email from our production servers and one from our test and development servers. If there are errors, then I know I have to take care of them before I can work on other stuff that day.

This procedure not only identifies the job failures and errors, it also returns the suspect pages on each server. Also, we log any failures that cause down time to our end users in Share Point. I've included a link to the Share Point page in the email message.

Requirements are that Database mail must be set up and remote servers must be a linked server.

Below the code is a sample email that I get.

USE [master]

GO

CREATE PROC [dbo].[SP_SQLJobFailureEmail]

AS /*

DATE: 7/14/06

WHO: Dave Ott

WHY: For easy reporting of errors to the DBA Group

This loops through the failures on these serrvers and reports the errors in an email

Server Other than this one must be linked servers

Mods:

10/10/06 Dave Ott

added server3

change logic to build datetime from run_date and run_time

6/25/07 Dave ott

Added suspect_pages

*/

SET NOCOUNT ON

-- list of errors

DECLARE @errors TABLE

(

Server VARCHAR(20),

Run_date DATETIME,

JobName VARCHAR(2000),

StepNumber TINYINT,

step_name VARCHAR(1000),

Message VARCHAR(2000),

Ukey INT IDENTITY(1, 1)

)

DECLARE @DaysToReport INT,

@Subject VARCHAR(100),

@Servers VARCHAR(300),

@Message VARCHAR(8000),

@EmailList VARCHAR(100),

@FromDate DATETIME,

@runDate INT,

@runTime INT

-- set defaults

SET @DaysToReport = 1 -- this will only report today and the yesturday

SET @EmailList = 'DBAGroup@MyCo.com' -- NT group for DBAs

SET @Subject = 'SQL Server Job Failure Email:'

SET @Servers = ''

SET @Message = ''

-- set up date and time for run

SET @FromDate = GETDATE() - @DaysToReport

SET @runDate = ( YEAR(@FromDate) * 10000 ) + ( MONTH(@FromDate) * 100 )

+ ( DAY(@FromDate) )

SET @runTime = DATEPART(hour, @FromDate) * 10000 + DATEPART(Minute, @FromDate)

* 100 + DATEPART(second, @FromDate)

-- server1 (here)

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM msdb.dbo.sysjobhistory h

JOIN msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(10), run_date),

4) + '-'

+ LEFT(RIGHT(CONVERT(VARCHAR(10), run_date), 4), 2) + '-'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2))

AND run_time < DATEPART(hh, @FromDate) * 10000

+ ( DATEPART(mi, @FromDate) * 100 ) + DATEPART(ss, @FromDate)

UNION

SELECT @@SERVERNAME,

GETDATE(),

'suspect_pages',

1,

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM msdb.dbo.suspect_pages

-- server2

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM server2.msdb.dbo.sysjobhistory h

JOIN server2.msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= LEFT(RIGHT(CONVERT(VARCHAR(10), run_date),

4), 2) + '/'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2) + '/'

+ LEFT(CONVERT(VARCHAR(10), run_date), 4) + ' '

+ LEFT(RIGHT('0' + CONVERT(VARCHAR(10), run_time), 6), 2)

+ ':' + LEFT(RIGHT(CONVERT(VARCHAR(10), run_time), 4), 2)

+ ':' + RIGHT(CONVERT(VARCHAR(10), run_time), 2)

UNION

SELECT 'server2',

GETDATE(),

'suspect_pages',

1,

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM server2.msdb.dbo.suspect_pages

--server2\Instance1

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM [server2\Instance1].msdb.dbo.sysjobhistory h

JOIN [server2\Instance1].msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= LEFT(RIGHT(CONVERT(VARCHAR(10), run_date),

4), 2) + '/'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2) + '/'

+ LEFT(CONVERT(VARCHAR(10), run_date), 4) + ' '

+ LEFT(RIGHT('0' + CONVERT(VARCHAR(10), run_time), 6), 2)

+ ':' + LEFT(RIGHT(CONVERT(VARCHAR(10), run_time), 4), 2)

+ ':' + RIGHT(CONVERT(VARCHAR(10), run_time), 2)

UNION

SELECT '[server2\Instance1]',

GETDATE(),

'suspect_pages',

1,

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM [server2\Instance1].msdb.dbo.suspect_pages

--server3

INSERT INTO @errors

SELECT DISTINCT

server,

CONVERT(VARCHAR(10), run_date),

name,

step_id,

step_name,

message

FROM [server3].msdb.dbo.sysjobhistory h

JOIN [server3].msdb.dbo.sysjobs j

ON h.job_id = j.job_id

WHERE run_status = 0

AND @FromDate <= LEFT(RIGHT(CONVERT(VARCHAR(10), run_date),

4), 2) + '/'

+ RIGHT(CONVERT(VARCHAR(10), run_date), 2) + '/'

+ LEFT(CONVERT(VARCHAR(10), run_date), 4) + ' '

+ LEFT(RIGHT('0' + CONVERT(VARCHAR(10), run_time), 6), 2)

+ ':' + LEFT(RIGHT(CONVERT(VARCHAR(10), run_time), 4), 2)

+ ':' + RIGHT(CONVERT(VARCHAR(10), run_time), 2)

UNION

SELECT '[server3]',

GETDATE(),

'suspect_pages',

1,

'msdb.dbo.suspect_pages',

'datebase_id=' + CONVERT(VARCHAR(2), database_id)

FROM [server3].msdb.dbo.suspect_pages

-- get list of server if any

WHILE EXISTS ( SELECT DISTINCT

server

FROM @errors

WHERE CHARINDEX(Server, @Servers, 1) = 0 )

BEGIN

SET @Servers = @Servers + ' '

+ ( SELECT TOP 1

Server

FROM @errors

WHERE CHARINDEX(Server, @Servers, 1) = 0

)

END

IF @Servers = ' ' -- if not error

BEGIN

-- send a plesent message

SET @Servers = 'No Failures'

END

ELSE

BEGIN

-- else replace space with comma

SET @Servers = REPLACE(LTRIM(RTRIM(@Servers)), ' ', ',')

END

SET @Subject = @Subject + ' ' + @Servers

SET @Message = ' Reporting from ' + CONVERT(VARCHAR(10), GETDATE()

- @DaysToReport, 110) + CHAR(13) + CHAR(13)

-- Format Email Message

IF @Servers NOT LIKE '%No Failures%'

BEGIN

-- Add link to record error in share point

SET @Message = @Message

+ 'Record Issues in SharePoint:http://SPServer/SiteDirectory/Numbers/Lists/BI%20Production%20Support%20Incidents/AllItems.aspx'

+ CHAR(13) + CHAR(13)

DECLARE @Server VARCHAR(20),

@Run_date DATETIME,

@JobName VARCHAR(2000),

@StepNumber TINYINT,

@step_name VARCHAR(1000),

@ErrMessage VARCHAR(2000),

@Ukey INT

WHILE EXISTS ( SELECT *

FROM @errors )

BEGIN

SET @Ukey = ( SELECT TOP 1

Ukey

FROM @errors

ORDER BY Server,

Run_date,

JobName

)

-- server

IF ISNULL(@Server, ' ') <> ( SELECT Server

FROM @errors

WHERE Ukey = @Ukey

)

BEGIN

SET @Server = ( SELECT Server

FROM @errors

WHERE Ukey = @Ukey

)

SET @Message = @Message + @Server + CHAR(13)

+ CHAR(10)

END

-- Run Date

IF ISNULL(@Run_date, ' ') <> ( SELECT Run_date

FROM @errors

WHERE Ukey = @Ukey

)

BEGIN

SET @Run_date = ( SELECT Run_date

FROM @errors

WHERE Ukey = @Ukey

)

SET @Message = @Message + ' '

+ CONVERT(VARCHAR(10), @Run_date, 110)

+ CHAR(13) + CHAR(10)

END

-- JOB

IF ISNULL(@JobName, ' ') <> ( SELECT JobName

FROM @errors

WHERE Ukey = @Ukey

)

BEGIN

SET @JobName = ( SELECT JobName

FROM @errors

WHERE Ukey = @Ukey

)

SET @Message = @Message + ' ' + @JobName

+ CHAR(13) + CHAR(10)

END

-- Job step

SET @StepNumber = ( SELECT MAX(StepNumber)

FROM @errors

WHERE JobName = @JobName

AND Server = @Server

)

SET @Message = @Message + ' Step:'

+ CONVERT(VARCHAR(3), @StepNumber) --+CHAR(13)+CHAR(10)

SET @ErrMessage = ( SELECT TOP 1

Message

FROM @errors

WHERE JobName = @JobName

AND Server = @Server

AND StepNumber = @StepNumber

)

SET @Message = @Message + '-' + @ErrMessage + CHAR(13)

+ CHAR(10) + CHAR(13) + CHAR(10)

-- Delete rows that we just put into the message

DELETE FROM @errors

WHERE JobName = @JobName

AND Server = @Server

DELETE FROM @errors

WHERE Ukey = @Ukey

END

END

SET @Message = @Message + CHAR(13) + CHAR(10)

+ 'Run From Sever1.Master.dbo.SP_SQLJobFailureEmail' + CHAR(13) + CHAR(10)

-- Mail it out!

--

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile',

@recipients = @EmailList, @body = @Message, @subject = @Subject,

@importance = 'High'

Here is an example email