Automating SQL Server Health Check (SQL Server 2005)

Question

Post reply

Automating SQL Server Health Check (SQL Server 2005)

Viewing 15 posts - 31 through 45 (of 140 total)

You must be logged in to reply to this topic. Login to reply

Gingkoo SSChasing Mays Points: 644 More actions · Answer 1

I tested this, after 2hrs 9 minutes i got he following error:

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72

An INSERT EXEC statement cannot be nested.

Mail queued.

ESL_HSBC SSC Veteran Points: 290 More actions · Answer 2

Hi,

doesn't work, i've the same error with @variable and nested insert and exec

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 3

@victor: the error can be ignore it does not stop the mail going out.

worry is why it took 2+ hours? how many databases are hosted on the server where u ran this sp? what the server config? memory, ram etc...

whats the sql edition ur using?

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 4

@ESL_HSBC: the error can be ignore it does not stop the mail going out.

if you are not gettin gthe mail then therz prob with mailprofile or mail server....

Gingkoo SSChasing Mays Points: 644 More actions · Answer 5

This bastardised from a number of different scripts from the internet...

USE [DBA_Maint]

GO

/****** Object: StoredProcedure [SQLStatus].[spDailyHealthCheck] Script Date: 01/28/2010 11:21:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [SQLStatus].[spDailyHealthCheck] AS

SET NOCOUNT ON

PRINT 'Declaring variables'

DECLARE @tableHTML nvarchar(max)

DECLARE @messagenvarchar(2000)

DECLARE@recipients nvarchar(2000)SET @recipients='*********@**************.***'

-- SET @recipients='victor.girling@avis-europe.com'

DECLARE @profile_name nvarchar(124)SET @profile_name = (SELECT name FROM msdb.dbo.sysmail_profile WHERE last_mod_datetime = (SELECT max(last_mod_datetime) FROM msdb.dbo.sysmail_profile))

DECLARE @subject nvarchar(255)SET @subject = 'Daily Health Check - Server: '+@@servername+' Date: '+ DATENAME(DAY,GETDATE())+' '+DATENAME(MONTH,GETDATE())+' '+DATENAME(YEAR,GETDATE())

BEGIN TRY

PRINT 'Building Db Size Statitics'

TRUNCATE TABLE SQLStatus.DailyHealthCheck

DECLARE @TargetDatabase sysnameSET @TargetDatabase = NULL -- NULL: all dbs

DECLARE @Level varchar(10)SET @Level = 'Database' -- or "File"

DECLARE @UpdateUsage bitSET @UpdateUsage = 0 -- default no update

DECLARE @Unit char(2)SET @Unit = 'GB' -- Megabytes, Kilobytes or

IF @TargetDatabase IS NOT NULL AND DB_ID(@TargetDatabase) IS NULL

BEGIN

RAISERROR(15010, -1, -1, @TargetDatabase);

RETURN (-1)

END

IF OBJECT_ID('tempdb.dbo.##Tbl_CombinedInfo', 'U') IS NOT NULL

DROP TABLE dbo.##Tbl_CombinedInfo;

IF OBJECT_ID('tempdb.dbo.##Tbl_DbFileStats', 'U') IS NOT NULL

DROP TABLE dbo.##Tbl_DbFileStats;

IF OBJECT_ID('tempdb.dbo.##Tbl_ValidDbs', 'U') IS NOT NULL

DROP TABLE dbo.##Tbl_ValidDbs;

IF OBJECT_ID('tempdb.dbo.##Tbl_Logs', 'U') IS NOT NULL

DROP TABLE dbo.##Tbl_Logs;

IF OBJECT_ID('tempdb.dbo.##Tbl_ServerErrorLog', 'U') IS NOT NULL

DROP TABLE dbo.##Tbl_ServerErrorLog;

CREATE TABLE dbo.##Tbl_ServerErrorLog (

LogDate datetime NOT NULL,

ProcessInfo nvarchar(40) NOT NULL,

Text nvarchar(2000) NULL);

CREATE TABLE dbo.##Tbl_CombinedInfo (

DatabaseName sysname NULL,

[type] VARCHAR(10) NULL,

LogicalName sysname NULL,

T dec(10, 2) NULL,

U dec(10, 2) NULL,

[U(%)] dec(5, 2) NULL,

F dec(10, 2) NULL,

[F(%)] dec(5, 2) NULL,

PhysicalName sysname NULL );

CREATE TABLE dbo.##Tbl_DbFileStats (

Id int identity,

DatabaseName sysname NULL,

FileId int NULL,

FileGroup int NULL,

TotalExtents bigint NULL,

UsedExtents bigint NULL,

Name sysname NULL,

FileName varchar(255) NULL );

CREATE TABLE dbo.##Tbl_ValidDbs (

Id int identity,

Dbname sysname NULL );

CREATE TABLE dbo.##Tbl_Logs (

DatabaseName sysname NULL,

LogSize dec (10, 2) NULL,

LogSpaceUsedPercent dec (5, 2) NULL,

Status int NULL );

DECLARE @Tbl_FreeDiskSpace TABLE (

drive char(1) NOT NULL,

FreeSpace bigint NOT NULL);

DECLARE @Tbl_UsedDiskSpace TABLE (

drive char(1) NOT NULL,

UsedSpace bigint NOT NULL);

DECLARE @Tbl_ChangeDiskSpace TABLE (

drive char(1) NOT NULL,

ChangeSpace bigint NOT NULL DEFAULT 0);

DECLARE @Ver varchar(10),

@DatabaseName sysname,

@Ident_last int,

@String varchar(2000),

@BaseString varchar(2000);

SELECT @DatabaseName = '',

@Ident_last = 0,

@String = '',

@Ver = CASE WHEN @@VERSION LIKE '%9.0%' THEN 'SQL 2005'

WHEN @@VERSION LIKE '%8.0%' THEN 'SQL 2000'

WHEN @@VERSION LIKE '%10.0%' THEN 'SQL 2008'

END;

SELECT @BaseString =

' SELECT DB_NAME(), ' +

CASE WHEN @Ver = 'SQL 2000' THEN 'CASE WHEN status & 0x40 = 0x40 THEN ''Log'' ELSE ''Data'' END'

ELSE ' CASE type WHEN 0 THEN ''Data'' WHEN 1 THEN ''Log'' WHEN 4 THEN ''Full-text'' ELSE ''reserved'' END' END +

', name, ' +

CASE WHEN @Ver = 'SQL 2000' THEN 'filename' ELSE 'physical_name' END +

', size*8.0/1024.0 FROM ' +

CASE WHEN @Ver = 'SQL 2000' THEN 'sysfiles' ELSE 'sys.database_files' END +

' WHERE '

+ CASE WHEN @Ver = 'SQL 2000' THEN ' HAS_DBACCESS(DB_NAME()) = 1' ELSE 'state_desc = ''ONLINE''' END + '';

SELECT @String = 'INSERT INTO dbo.##Tbl_ValidDbs SELECT name FROM ' +

CASE WHEN @Ver = 'SQL 2000' THEN 'master.dbo.sysdatabases'

WHEN @Ver IN ('SQL 2005', 'SQL 2008') THEN 'master.sys.databases'

END + ' WHERE HAS_DBACCESS(name) = 1 ORDER BY name ASC';

EXEC (@String);

INSERT INTO dbo.##Tbl_Logs EXEC ('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS');

-- For data part

IF @TargetDatabase IS NOT NULL

BEGIN

SELECT @DatabaseName = @TargetDatabase;

IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName,'Status') = 'ONLINE'

AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'

BEGIN

SELECT @String = 'USE [' + @DatabaseName + '] DBCC UPDATEUSAGE (0)';

PRINT '*** ' + @String + ' *** ';

EXEC (@String);

PRINT '';

END

SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;

INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)

EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');

EXEC ('USE [' + @DatabaseName + '] ' + @String);

UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName;

END

ELSE

BEGIN

WHILE 1 = 1

BEGIN

SELECT TOP 1 @DatabaseName = Dbname FROM dbo.##Tbl_ValidDbs WHERE Dbname > @DatabaseName ORDER BY Dbname ASC;

IF @@ROWCOUNT = 0

BREAK;

IF @UpdateUsage <> 0 AND DATABASEPROPERTYEX (@DatabaseName, 'Status') = 'ONLINE'

AND DATABASEPROPERTYEX (@DatabaseName, 'Updateability') <> 'READ_ONLY'

BEGIN

SELECT @String = 'DBCC UPDATEUSAGE (''' + @DatabaseName + ''') ';

PRINT '*** ' + @String + '*** ';

EXEC (@String);

PRINT '';

END

SELECT @Ident_last = ISNULL(MAX(Id), 0) FROM dbo.##Tbl_DbFileStats;

SELECT @String = 'INSERT INTO dbo.##Tbl_CombinedInfo (DatabaseName, type, LogicalName, PhysicalName, T) ' + @BaseString;

EXEC ('USE [' + @DatabaseName + '] ' + @String);

INSERT INTO dbo.##Tbl_DbFileStats (FileId, FileGroup, TotalExtents, UsedExtents, Name, FileName)

EXEC ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS');

UPDATE dbo.##Tbl_DbFileStats SET DatabaseName = @DatabaseName WHERE Id BETWEEN @Ident_last + 1 AND @@IDENTITY;

END

-- set used size for data files, do not change total obtained from sys.database_files as it has for log files

UPDATE dbo.##Tbl_CombinedInfo

SET U = s.UsedExtents*8*8/1024.0

FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_DbFileStats s

ON t.LogicalName = s.Name AND s.DatabaseName = t.DatabaseName;

-- set used size and % values for log files:

UPDATE dbo.##Tbl_CombinedInfo

SET [U(%)] = LogSpaceUsedPercent,

U = T * LogSpaceUsedPercent/100.0

FROM dbo.##Tbl_CombinedInfo t JOIN dbo.##Tbl_Logs l

ON l.DatabaseName = t.DatabaseName

WHERE t.type = 'Log';

UPDATE dbo.##Tbl_CombinedInfo SET F = T - U, [U(%)] = U*100.0/T;

UPDATE dbo.##Tbl_CombinedInfo SET [F(%)] = F*100.0/T;

IF UPPER(ISNULL(@Level, 'DATABASE')) = 'FILE'

BEGIN

IF @Unit = 'KB'

UPDATE dbo.##Tbl_CombinedInfo

SET T = T * 1024, U = U * 1024, F = F * 1024;

IF @Unit = 'GB'

UPDATE dbo.##Tbl_CombinedInfo

SET T = T / 1024, U = U / 1024, F = F / 1024;

SELECT DatabaseName AS 'Database',

type AS 'Type',

LogicalName,

T AS 'Total',

U AS 'Used',

[U(%)] AS 'Used (%)',

F AS 'Free',

[F(%)] AS 'Free (%)',

PhysicalName

FROM dbo.##Tbl_CombinedInfo

WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')

ORDER BY DatabaseName ASC, type ASC;

SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',

SUM (T) AS 'TOTAL', SUM (U) AS 'USED', SUM (F) AS 'FREE' FROM dbo.##Tbl_CombinedInfo;

END

IF UPPER(ISNULL(@Level, 'DATABASE')) = 'DATABASE'

BEGIN

DECLARE @Tbl_Final TABLE (

DatabaseName sysname NULL,

TOTAL dec (10, 2),

[=] char(1),

used dec (10, 2),

[used (%)] dec (5, 2),

[+] char(1),

free dec (10, 2),

[free (%)] dec (5, 2),

[==] char(2),

Data dec (10, 2),

Data_Used dec (10, 2),

[Data_Used (%)] dec (5, 2),

Data_Free dec (10, 2),

[Data_Free (%)] dec (5, 2),

[++] char(2),

Log dec (10, 2),

Log_Used dec (10, 2),

[Log_Used (%)] dec (5, 2),

Log_Free dec (10, 2),

[Log_Free (%)] dec (5, 2) );

INSERT INTO @Tbl_Final

SELECT x.DatabaseName,

x.Data + y.Log AS 'TOTAL',

'=' AS '=',

x.Data_Used + y.Log_Used AS 'U',

(x.Data_Used + y.Log_Used)*100.0 / (x.Data + y.Log) AS 'U(%)',

'+' AS '+',

x.Data_Free + y.Log_Free AS 'F',

(x.Data_Free + y.Log_Free)*100.0 / (x.Data + y.Log) AS 'F(%)',

'==' AS '==',

x.Data,

x.Data_Used,

x.Data_Used*100/x.Data AS 'D_U(%)',

x.Data_Free,

x.Data_Free*100/x.Data AS 'D_F(%)',

'++' AS '++',

y.Log,

y.Log_Used,

y.Log_Used*100/y.Log AS 'L_U(%)',

y.Log_Free,

y.Log_Free*100/y.Log AS 'L_F(%)'

FROM

( SELECT d.DatabaseName,

SUM(d.T) AS 'Data',

SUM(d.U) AS 'Data_Used',

SUM(d.F) AS 'Data_Free'

FROM dbo.##Tbl_CombinedInfo d WHERE d.type = 'Data' GROUP BY d.DatabaseName ) AS x

JOIN

( SELECT l.DatabaseName,

SUM(l.T) AS 'Log',

SUM(l.U) AS 'Log_Used',

SUM(l.F) AS 'Log_Free'

FROM dbo.##Tbl_CombinedInfo l WHERE l.type = 'Log' GROUP BY l.DatabaseName ) AS y

ON x.DatabaseName = y.DatabaseName;

IF @Unit = 'KB'

UPDATE @Tbl_Final SET TOTAL = TOTAL * 1024,

used = used * 1024,

free = free * 1024,

Data = Data * 1024,

Data_Used = Data_Used * 1024,

Data_Free = Data_Free * 1024,

Log = Log * 1024,

Log_Used = Log_Used * 1024,

Log_Free = Log_Free * 1024;

IF @Unit = 'GB'

UPDATE @Tbl_Final SET TOTAL = TOTAL / 1024,

used = used / 1024,

free = free / 1024,

Data = Data / 1024,

Data_Used = Data_Used / 1024,

Data_Free = Data_Free / 1024,

Log = Log / 1024,

Log_Used = Log_Used / 1024,

Log_Free = Log_Free / 1024;

DECLARE @GrantTotal dec(11, 2);

SELECT @GrantTotal = SUM(TOTAL) FROM @Tbl_Final;

INSERT INTO @Tbl_FreeDiskSpace (drive,FreeSpace)

EXEC ('EXEC master..xp_fixeddrives')

INSERT INTO @Tbl_ChangeDiskSpace (drive,ChangeSpace)

SELECT f.[Drive],isnull([UsedSpace],0)

FROM @Tbl_FreeDiskSpace f

LEFT OUTER JOIN [SQLStatus].[DailyDiskSpaceCheck] ddsc ON f.drive=ddsc.drive

INSERT INTO @Tbl_UsedDiskSpace (drive,UsedSpace)

SELECT substring(PhysicalName,1,1),sum(U)

FROM dbo.##Tbl_CombinedInfo

GROUP BY substring(PhysicalName,1,1)

TRUNCATE TABLE [SQLStatus].[DailyDiskSpaceCheck]

INSERT INTO [SQLStatus].[DailyDiskSpaceCheck] ([Drive],[UsedSpace],[FreeSpace],[Change])

SELECT c.drive,UsedSpace/1000,FreeSpace/1000,(ChangeSpace/1000)-(UsedSpace/1000)

FROM @Tbl_ChangeDiskSpace c

INNER JOIN @Tbl_UsedDiskSpace u ON c.drive=u.drive

INNER JOIN @Tbl_FreeDiskSpace f ON c.drive=f.drive

INSERT INTO SQLStatus.DailyHealthCheck ([DatabaseName],[DataSize],[DataSpaceUsed],[LogSize],[LogSpaceUsed])

SELECT

DatabaseName AS 'DATABASE',

-- CONVERT(VARCHAR(12), used) + ' (' + CONVERT(VARCHAR(12), [used (%)]) + ' %)' AS 'USED (%)',

-- CONVERT(VARCHAR(12), free) + ' (' + CONVERT(VARCHAR(12), [free (%)]) + ' %)' AS 'FREE (%)',

CONVERT(VARCHAR(12), Data_Used),

CONVERT(VARCHAR(12), [Data_Used (%)]),

CONVERT(VARCHAR(12), Log_Used) ,

CONVERT(VARCHAR(12), [Log_Used (%)])

FROM @Tbl_Final

WHERE DatabaseName LIKE ISNULL(@TargetDatabase, '%')

ORDER BY DatabaseName ASC;

/*

IF @TargetDatabase IS NULL

SELECT CASE WHEN @Unit = 'GB' THEN 'GB' WHEN @Unit = 'KB' THEN 'KB' ELSE 'MB' END AS 'SUM',

SUM (used) AS 'USED',

SUM (free) AS 'FREE',

SUM (TOTAL) AS 'TOTAL',

SUM (Data) AS 'DATA',

SUM (Log) AS 'LOG'

FROM @Tbl_Final;

*/

END

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

SET @Message = 'Daily Health Check failed to send ERROR: '+ERROR_MESSAGE()

EXEC xp_logevent 60000, @message, informational

RETURN

END CATCH

BEGIN TRY

PRINT 'Updating database status'

UPDATE SQLStatus.DailyHealthCheck

SET DatabaseStatus = state_desc

FROM sys.databases db

WHERE db.name = SQLStatus.DailyHealthCheck.DatabaseName

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

SET @Message = 'Daily Health Check failed to send ERROR: '+ERROR_MESSAGE()

EXEC xp_logevent 60000, @message, informational

RETURN

END CATCH

BEGIN TRY

PRINT 'Checking backup dates'

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate

INTO #DBBackup

FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A

ON A.database_name = B.name AND A.type = 'D'

GROUP BY B.Name ORDER BY B.name

UPDATE SQLStatus.DailyHealthCheck

SET SQLStatus.DailyHealthCheck.DaysSinceLastBackup = #DBBackup.DaysSinceLastBackup, SQLStatus.DailyHealthCheck.LastBackupDate=#DBBackup.LastBackupDate

FROM #DBBackup

WHERE SQLStatus.DailyHealthCheck.DatabaseName=Database_Name

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

SET @Message = 'Daily Health Check failed to send ERROR: '+ERROR_MESSAGE()

EXEC xp_logevent 60000, @message, informational

RETURN

END CATCH

BEGIN TRY

INSERT INTO dbo.##Tbl_ServerErrorLog

EXEC ('xp_readerrorlog 0,1')

DECLARE @DateCheck datetime

SET @DateCheck = CONVERT(datetime,convert(varchar(11),DATEADD(DAY,-1,GETDATE()))+' 18:00')

TRUNCATE TABLE SQLStatus.ServerErrorLog

INSERT INTO SQLStatus.ServerErrorLog (LogDate,[Text])

SELECT LogDate,[Text] FROM dbo.##Tbl_ServerErrorLog

WHERE [Text] NOT LIKE '%Log backed up%' AND

[Text] NOT LIKE '%.TRN%' AND [Text] NOT LIKE '%Database backed up%' AND

[Text] NOT LIKE '%.BAK%' AND [Text] NOT LIKE '%Run the RECONFIGURE%' AND

[Text] NOT LIKE '%Copyright (c)%' AND LogDate > @DateCheck

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

SET @Message = 'Daily Health Check failed to send ERROR: '+ERROR_MESSAGE()

EXEC xp_logevent 60000, @message, informational

RETURN

END CATCH

BEGIN TRY

PRINT 'Building HTML'

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'distribution')

IF (SELECT count(1) FROM distribution..MSpublications) > 0

SET @tableHTML =

N'<H1>Daily Health Check</H1>' +

N'<H3>Server Status</H3>' +

N'<H4>Database Health Check</H4>' +

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

N'<tr><th>Database Name</th><th>Status</th><th>Data Size GB</th><th>Data Space Used %</th><th>Log Size GB</th><th>Log Space Used %</th><th>Days since last backup</th><th>Last backup date</th>'+

CAST ( ( SELECT td = rtrim(DatabaseName), '',

td = rtrim(DatabaseStatus), '',

td = DataSize, '',

td = DataSpaceUsed, '',

td = LogSize, '',

td = LogSpaceUsed, '',

td = DaysSinceLastBackup, '',

td = LastBackupDate, ''

FROM SQLStatus.DailyHealthCheck

FOR XML PATH('tr'), TYPE) AS nvarchar(max) ) +

N'</table>'+

N'<H4>Disk Usage</H4>'+

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

N'<tr><th>Drive</th><th>Disk Used GB</th><th>Disk Free GB</th><th>Change in Usage GB</th><th>Percentage Changed %</th>'+

CAST ( ( SELECT td = Drive, '',

td = UsedSpace, '',

td = FreeSpace, '',

td = Change , '',

td = CASE UsedSpace WHEN 0 THEN 0 ELSE Change/UsedSpace*100 END , ''

FROM [SQLStatus].[DailyDiskSpaceCheck]

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>'+

N'<H3>Job Status</H3>' +

N'<H4>Long running jobs</H4>' +

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

N'<tr><th>Job Name</th><th>start_execution_date</th>' +

isnull(CAST ( ( SELECT td = rtrim(J.name), '',

td = rtrim(start_execution_date), ''

FROM msdb.dbo.sysjobactivity JA

INNER JOIN msdb.dbo.sysjobs J

ON JA.job_id = J.job_id

WHERE DATEDIFF(hh,start_execution_date,GETDATE())> 3

AND stop_execution_date IS NULL

FOR XML PATH('tr') , TYPE ) AS nvarchar(max) )

, (SELECT CAST ( (SELECT td = '',td = '' FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) ) ) +

N'</table>' +

N'<H4>Job Status</H4>' +

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

N'<tr><th>Job Name</th><th>Step Name</th><th>Current Status</th><th>Last Outcome</th><th>Exit Message</th>' +

CAST ( ( SELECT td = rtrim(name), '',

td = rtrim(sjs.step_name), '',

td = CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END, '',

td = CASE last_run_outcome WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE 'Unknown' END, '',

td = jh.[message], ''

FROMmsdb..sysjobs sj

INNER JOIN msdb..sysjobsteps sjs ON sj.job_id=sjs.job_id

INNER JOIN msdb..sysjobactivity ja ON ja.job_id=sjs.job_id

INNER JOIN msdb..sysjobhistory jh ON ja.job_history_id=jh.instance_id

INNER JOIN (SELECT ja1.job_id,max(start_execution_date) AS start_execution_date,max(jh1.instance_id) AS instance_id

FROM msdb..sysjobactivity ja1

INNER JOIN msdb..sysjobhistory jh1 ON ja1.job_history_id=jh1.instance_id

GROUP BY ja1.job_id) AS CA

ON jh.instance_id=CA.instance_id

WHEREenabled = 1 -- AND jh.run_status != 1

ORDER BY name ASC

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' +

N'<H3>Replication Status</H3>' +

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

N'<tr><th>Publication</th><th>Agent Name</th><th>Replication Status</th><th>Current Latency</th><th>Average Latency</th><th>Worst Latency</th>' +

CAST ( ( SELECT td = p.publication, '',

td = rmd.agent_name, '',

td = CASE rmd.status WHEN 1 THEN 'Started' WHEN 2 THEN 'Succeeded' WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retrying' WHEN 6 THEN 'Failed' END, '',

td = rmd.cur_latency, '',

td = rmd.avg_latency, '',

td = rmd.worst_latency, ''

FROM distribution..MSreplication_monitordata rmd

INNER JOIN distribution..MSpublications p ON rmd.publication_id=p.publication_id

ORDER BY p.publication ASC,agent_name ASC

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' +

N'<H3>SQL Server Error Logs</H3>' +

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

N'<tr><th>Log Date</th><th>Log Text</th>' +

CAST ( ( SELECT td = LogDate, '',

td = [Text], ''

FROM SQLStatus.ServerErrorLog

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' ;

ELSE

SET @tableHTML =

N'<H1>Daily Health Check</H1>' +

N'<H3>Server Status</H3>' +

N'<H4>Database Health Check</H4>' +

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

N'<tr><th>Database Name</th><th>Status</th><th>Data Size GB</th><th>Data Space Used %</th><th>Log Size GB</th><th>Log Space Used %</th><th>Days since last backup</th><th>Last backup date</th>'+

CAST ( ( SELECT td = rtrim(DatabaseName), '',

td = rtrim(DatabaseStatus), '',

td = DataSize, '',

td = DataSpaceUsed, '',

td = LogSize, '',

td = LogSpaceUsed, '',

td = DaysSinceLastBackup, '',

td = LastBackupDate, ''

FROM SQLStatus.DailyHealthCheck

FOR XML PATH('tr'), TYPE) AS nvarchar(max) ) +

N'</table>'+

N'<H4>Disk Usage</H4>'+

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

N'<tr><th>Drive</th><th>Disk Used GB</th><th>Disk Free GB</th><th>Change in Usage GB</th><th>Percentage Changed %</th>'+

CAST ( ( SELECT td = Drive, '',

td = UsedSpace, '',

td = FreeSpace, '',

td = Change , '',

td = CASE UsedSpace WHEN 0 THEN 0 ELSE Change/UsedSpace*100 END , ''

FROM [SQLStatus].[DailyDiskSpaceCheck]

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>'+

N'<H4>Long running jobs</H4>' +

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

N'<tr><th>Job Name</th><th>start_execution_date</th>' +

isnull(CAST ( ( SELECT td = rtrim(J.name), '',

td = rtrim(start_execution_date), ''

FROM msdb.dbo.sysjobactivity JA

INNER JOIN msdb.dbo.sysjobs J

ON JA.job_id = J.job_id

WHERE DATEDIFF(hh,start_execution_date,GETDATE())> 3

AND stop_execution_date IS NULL

FOR XML PATH('tr') , TYPE ) AS nvarchar(max) )

, (SELECT CAST ( (SELECT td = '',td = '' FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) ) ) +

N'</table>' +

N'<H4>Job Status</H4>' +

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

N'<tr><th>Job Name</th><th>Step Name</th><th>Current Status</th><th>Last Outcome</th><th>Exit Message</th>' +

CAST ( ( SELECT td = rtrim(name), '',

td = rtrim(sjs.step_name), '',

td = CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END, '',

td = CASE last_run_outcome WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE 'Unknown' END, '',

td = jh.[message], ''

FROMmsdb..sysjobs sj

INNER JOIN msdb..sysjobsteps sjs ON sj.job_id=sjs.job_id

INNER JOIN msdb..sysjobactivity ja ON ja.job_id=sjs.job_id

INNER JOIN msdb..sysjobhistory jh ON ja.job_history_id=jh.instance_id

INNER JOIN (SELECT ja1.job_id,max(start_execution_date) AS start_execution_date,max(jh1.instance_id) AS instance_id

FROM msdb..sysjobactivity ja1

INNER JOIN msdb..sysjobhistory jh1 ON ja1.job_history_id=jh1.instance_id

GROUP BY ja1.job_id) AS CA

ON jh.instance_id=CA.instance_id

WHEREenabled = 1 -- AND jh.run_status != 1

ORDER BY name ASC

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' +

N'<H3>SQL Server Error Logs</H3>' +

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

N'<tr><th>Log Date</th><th>Log Text</th>' +

CAST ( ( SELECT td = LogDate, '',

td = [Text], ''

FROM SQLStatus.ServerErrorLog

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' ;

ELSE

SET @tableHTML =

N'<H1>Daily Health Check</H1>' +

N'<H3>Server Status</H3>' +

N'<H4>Database Health Check</H4>' +

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

N'<tr><th>Database Name</th><th>Status</th><th>Data Size GB</th><th>Data Space Used %</th><th>Log Size GB</th><th>Log Space Used %</th><th>Days since last backup</th><th>Last backup date</th>'+

CAST ( ( SELECT td = rtrim(DatabaseName), '',

td = rtrim(DatabaseStatus), '',

td = DataSize, '',

td = DataSpaceUsed, '',

td = LogSize, '',

td = LogSpaceUsed, '',

td = DaysSinceLastBackup, '',

td = LastBackupDate, ''

FROM SQLStatus.DailyHealthCheck

FOR XML PATH('tr'), TYPE) AS nvarchar(max) ) +

N'</table>'+

N'<H4>Disk Usage</H4>'+

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

N'<tr><th>Drive</th><th>Disk Used GB</th><th>Disk Free GB</th><th>Change in Usage GB</th><th>Percentage Changed %</th>'+

CAST ( ( SELECT td = Drive, '',

td = UsedSpace, '',

td = FreeSpace, '',

td = Change , '',

td = CASE UsedSpace WHEN 0 THEN 0 ELSE Change/UsedSpace*100 END , ''

FROM [SQLStatus].[DailyDiskSpaceCheck]

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>'+

N'<H4>Long running jobs</H4>' +

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

N'<tr><th>Job Name</th><th>start_execution_date</th>' +

isnull(CAST ( ( SELECT td = rtrim(J.name), '' ,

td = rtrim(start_execution_date), ''

FROM msdb.dbo.sysjobactivity JA

INNER JOIN msdb.dbo.sysjobs J

ON JA.job_id = J.job_id

WHERE DATEDIFF(hh,start_execution_date,GETDATE())> 3

AND stop_execution_date IS NULL

FOR XML PATH('tr') , TYPE ) AS nvarchar(max) )

, (SELECT CAST ( (SELECT td = '',td = '' FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) ) ) +

N'</table>' +

N'<H4>Job Status</H4>' +

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

N'<tr><th>Job Name</th><th>Step Name</th><th>Current Status</th><th>Last Outcome</th><th>Exit Message</th>' +

CAST ( ( SELECT td = rtrim(name), '',

td = rtrim(sjs.step_name), '',

td = CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END, '',

td = CASE last_run_outcome WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE 'Unknown' END, '',

td = jh.[message], ''

FROMmsdb..sysjobs sj

INNER JOIN msdb..sysjobsteps sjs ON sj.job_id=sjs.job_id

INNER JOIN msdb..sysjobactivity ja ON ja.job_id=sjs.job_id

INNER JOIN msdb..sysjobhistory jh ON ja.job_history_id=jh.instance_id

INNER JOIN (SELECT ja1.job_id,max(start_execution_date) AS start_execution_date,max(jh1.instance_id) AS instance_id

FROM msdb..sysjobactivity ja1

INNER JOIN msdb..sysjobhistory jh1 ON ja1.job_history_id=jh1.instance_id

GROUP BY ja1.job_id) AS CA

ON jh.instance_id=CA.instance_id

WHEREenabled = 1 -- AND jh.run_status != 1

ORDER BY name ASC

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' +

N'<H3>SQL Server Error Logs</H3>' +

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

N'<tr><th>Log Date</th><th>Log Text</th>' +

CAST ( ( SELECT td = LogDate, '',

td = [Text], ''

FROM SQLStatus.ServerErrorLog

FOR XML PATH('tr'), TYPE ) AS nvarchar(max) ) +

N'</table>' ;

-- master..xp_fixeddrives

PRINT 'Sending Email'

EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients,

@profile_name = @profile_name,

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML' ;

END TRY

BEGIN CATCH

PRINT ERROR_MESSAGE()

SET @Message = 'Daily Health Check failed to send ERROR: '+ERROR_MESSAGE()

EXEC xp_logevent 60000, @message, informational

RETURN

END CATCH

SET @message = 'The Daily Health Check has been sent to '+@recipients

EXEC xp_logevent 60000, @message, informational

DROP TABLE #DBBackup

RETURN

Gingkoo SSChasing Mays Points: 644 More actions · Answer 6

If your interested this will also help. The status report requires three tables..

USE [DBA_Maint]

GO

/****** Object: Table [SQLStatus].[ServerErrorLog] Script Date: 01/28/2010 11:50:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [SQLStatus].[ServerErrorLog](

[LogDate] [datetime] NOT NULL,

[Text] [nvarchar](2000) NULL

) ON [PRIMARY]

GO

/****** Object: Table [SQLStatus].[DailyHealthCheck] Script Date: 01/28/2010 11:50:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [SQLStatus].[DailyHealthCheck](

[DatabaseName] [nvarchar](124) NOT NULL,

[DatabaseStatus] [nvarchar](60) NULL,

[DaysSinceLastBackup] [nvarchar](20) NULL,

[LastBackupDate] [nvarchar](20) NULL,

[DataSize] [nvarchar](20) NOT NULL,

[DataSpaceUsed] [nvarchar](20) NOT NULL,

[LogSize] [nvarchar](20) NOT NULL,

[LogSpaceUsed] [nvarchar](20) NOT NULL

) ON [PRIMARY]

GO

/****** Object: Table [SQLStatus].[DailyDiskSpaceCheck] Script Date: 01/28/2010 11:50:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [SQLStatus].[DailyDiskSpaceCheck](

[Drive] [char](1) NOT NULL,

[UsedSpace] [bigint] NOT NULL,

[FreeSpace] [bigint] NOT NULL,

[Change] [bigint] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Default [DF__DailyDisk__Chang__1DB06A4F] Script Date: 01/28/2010 11:50:49 ******/

ALTER TABLE [SQLStatus].[DailyDiskSpaceCheck] ADD DEFAULT ((0)) FOR [Change]

GO

/****** Object: Default [DF__DailyHeal__Datab__1BC821DD] Script Date: 01/28/2010 11:50:49 ******/

ALTER TABLE [SQLStatus].[DailyHealthCheck] ADD DEFAULT ('Unknown') FOR [DatabaseStatus]

GO

steeled SSC Enthusiast Points: 178 More actions · Answer 7

Will running the script, I ran into this....

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72

An INSERT EXEC statement cannot be nested.

Mail queued.

The email is delivered, but I cannot resolve the above error. Any ideas?

phemmer Old Hand Points: 395 More actions · Answer 8

Hi - I tried to run it, I get this error:

sp_get_composite_job_info, Line 68

An INSERT EXEC statement cannot be nested.

what do I have to change?

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 9

@steeled: as i mentioned in my previous posts this error can be ignore it does not stop the mail going out neither it affect data integrity.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 10

@phemmer: as i mentioned in my previous posts this error can be ignore it does not stop the mail going out neither it affect data integrity.

steeled SSC Enthusiast Points: 178 More actions · Answer 11

Sorry for the report. I neglected to read the entire thread. Sorry.

I wanted to also mention that I get no Job Status'. I get the 'job name', 'enabled', 'last run', etc..., but no status. The formatting of the email seems to be messed up as well.

phemmer Old Hand Points: 395 More actions · Answer 12

me too. for some reason first load didn't show me there was more than 1 page of replies..

nice work!

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 13

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 7:41 am

#1110019

thx phemmer

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 14

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 7:41 am

#1110020

steeled, what do u exactly mean by job status?

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 15

steeled, also can you fwd me the mail on riteshmedhe@rediffmail.com? let me check what has got messed up.... thx

Automating SQL Server Health Check (SQL Server 2005)

Cookies on SQLServerCentral