January 28, 2010 at 4:10 am
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.
January 28, 2010 at 4:16 am
Hi,
doesn't work, i've the same error with @variable and nested insert and exec
January 28, 2010 at 4:26 am
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
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
January 28, 2010 at 4:52 am
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
January 28, 2010 at 7:10 am
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?
January 28, 2010 at 7:13 am
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?
January 28, 2010 at 7:16 am
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.
January 28, 2010 at 7:21 am
me too. for some reason first load didn't show me there was more than 1 page of replies..
nice work!
January 28, 2010 at 7:41 am
thx phemmer
January 28, 2010 at 7:41 am
steeled, what do u exactly mean by job status?
January 28, 2010 at 7:43 am
steeled, also can you fwd me the mail on riteshmedhe@rediffmail.com? let me check what has got messed up.... thx
Viewing 15 posts - 31 through 45 (of 140 total)
You must be logged in to reply to this topic. Login to reply