Integrity Check file sender Script

These first five or so posts use DB mail and the stored procedure sp_send_dbmail. I like information sent to me, rather than me looking for it. In this case the weekly Database integrity checks are run and with over ten instances of SQL Server (all 2005). I don't want to spend my time every Monday morning (when I remember) to go look for each intergrity check output file.

Thinking about this, I should probably turn this into a SSIS package. That will be another post.

Each server needs DB mail running, it's easier if there is a common DB mail profile across all servers. This stored procedure should run on each machine and it would be one less thing to change in the code.

The code also uses xp_cmdshell. It's used to get directory listing where the intergrity output file exists. Here is the DOS command I use to get the list:
dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\*Integrity*.txt" /B /TC /O-D

where /B is base formating, /TC show time of creation and /O-D means order by desending date. So this will give us a list of files with the newest file at the top of the list. By dumping this into a temp table and getting the first one we have the file we want. Then we just have to mail it.
Code: /*

created by Dave Ott

This emails the Database Intergrity checks to me. PLace on all DB servers

Note: db mail must be set up and use the profile that is on that server

*/

-- OS command to get file. Change path as needed

DECLARE @cmd NVARCHAR(500)

SET @cmd = 'dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\*Integrity*.txt" /B /TC /O-D'

/*

/B - base format

/TC - Time, Creation

/O - List Files by (D)ate (-) reversed order

*/

SET nocount ON

CREATE TABLE #list

(

ikey INT IDENTITY(1, 1)

NOT NULL,

filename VARCHAR(1000)

)

INSERT INTO #list ( filename )

EXEC xp_cmdshell @cmd

--Most servers have only one file, but keep logic to loop through multiple files

DELETE FROM #list

WHERE ikey > 2

DECLARE @file VARCHAR(1000),

@file_attachments VARCHAR(3000)

WHILE EXISTS ( SELECT * FROM #list )

BEGIN

SET @file = (SELECT MIN(filename) FROM #list)

SET @file_attachments = CASE WHEN @file_attachments IS NULL THEN ''

ELSE @file_attachments + ';'END

+ 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\'

+ @file

DELETE FROM #list

WHERE filename = @file

END

-- Email file

DECLARE @subject NVARCHAR(100)

SET @subject = 'Integrity checks - ' + @@servername

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

@recipients = 'DBAGroup@MyCo.com', @body = 'Integrity check',

@subject = @subject, @file_attachments = @file_attachments,

@importance = 'High'

GO

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