DBA Daily Checks Email Report

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks_Backups](
	@HTML varchar(max) OUT,
	@FullWarningThresholdDays int,
	@DiffWarningThresholdDays int,
	@TranWarningThresHoldHours int
)
AS
SET NOCOUNT ON
/* 
	Returns HTML for "Backups" section of DBA Checks Report
*/ 
declare @Server varchar(40)
set @server = CONVERT(varchar(35), SERVERPROPERTY('machinename'))+ COALESCE('\'+ CONVERT(varchar(35), SERVERPROPERTY('instancename')),'')

DECLARE @backuplog TABLE(
	DBServer sysname,
	DBName sysname,
	LastFullBackup datetime,
	LastDiffBackup datetime,
	LastTranBackup datetime,
	DiffDays int,
	FullDays int,
	TranHours int
)

INSERT INTO @backuplog
SELECT fullrec.server_name [DBServer],
	 fullrec.database_name, 
	 fullrec.backup_finish_date LastFullBackup,
	 diffrec.backup_finish_date LastDiffBackup,
	 tranrec.backup_finish_date LastTranBackup,
	 datediff(dd,diffrec.backup_finish_date,getdate()) DiffDays,
	 datediff(dd,fullrec.backup_finish_date,getdate()) FullDays,
	 datediff(hh,tranrec.backup_finish_date,getdate()) TranHours
FROM msdb..backupset as fullrec
left outer join msdb..backupset as tranrec 
	on tranrec.database_name = fullrec.database_name
			and tranrec.server_name = fullrec.server_name
			and tranrec.type = 'L'
			and tranrec.backup_finish_date =
			  ((select max(backup_finish_date) 
				  from msdb..backupset b2 
				 where b2.database_name = fullrec.database_name
				   and b2.server_name = fullrec.server_name 
				   and b2.type = 'L'))
left outer join msdb..backupset as diffrec
	on diffrec.database_name = fullrec.database_name
		and diffrec.server_name = fullrec.server_name
		and diffrec.type = 'I'
		and diffrec.backup_finish_date =
		  ((select max(backup_finish_date) 
			  from msdb..backupset b2 
			where b2.database_name = fullrec.database_name 
			   and b2.server_name = fullrec.server_name 
			   and b2.type = 'I'))
where fullrec.type = 'D' -- full backups only
	and fullrec.backup_finish_date = 
	   (select max(backup_finish_date) 
		  from msdb..backupset b2 
		 where b2.database_name = fullrec.database_name 
		   and b2.server_name = fullrec.server_name 
		   and b2.type = 'D')
	and fullrec.database_name in (select name from master..sysdatabases) 
	and fullrec.database_name <> 'tempdb'
Union all
-- never backed up
select @server
		,name 
		,null
		,NULL
		,NULL 
		,NULL
		,NULL
		,NULL
from sys.databases as record
where not exists (select * from msdb..backupset where record.name = database_name and server_name = @server)
and name <> 'tempdb'
and source_database_id is null --exclude snapshots

SET @HTML = '<h2>Backups</h2>
			<table>
			<tr>
			<th>DB Server</th>
			<th>DB Name</th>
			<th>Last Full Backup</th>
			<th>Last Diff Backup</th>
			<th>Last Tran Backup</th>
			<th>Full Days</th>
			<th>Diff Days</th>
			<th>Tran Hours</th>
			<th>Recovery Model</th>
			</tr>' 
			+(SELECT DBServer td,
					DBName td,
					CAST(CASE WHEN FullDays IS NULL THEN '<div class="Critical">None/Unknown</div>'
						WHEN FullDays > @FullWarningThresholdDays THEN '<div class="Warning">' + LEFT(CONVERT(varchar,LastFullBackup,113),17) + '</div>'
						ELSE '<div class="Healthy">' + LEFT(CONVERT(varchar,LastFullBackup,113),17) + '</div>' END as XML) td,
					CAST(CASE WHEN DiffDays IS NULL THEN '<div class="Critical">None/Unknown</div>'
						WHEN DiffDays > @DiffWarningThresholdDays THEN '<div class="Warning">' + LEFT(CONVERT(varchar,LastDiffBackup,113),17) + '</div>'
						ELSE  '<div class="Healthy">' + LEFT(CONVERT(varchar,LastDiffBackup,113),17) + '</div>' END as XML) td,
					CAST(CASE WHEN TranHours IS NULL THEN  COALESCE(LEFT(NULLIF(recovery_model_desc,'SIMPLE'),0) + '<div class="Critical">None/Unknown</div>','N/A')
						WHEN TranHours > @TranWarningThresholdHours THEN '<div class="Warning">' + LEFT(CONVERT(varchar,LastTranBackup,113),17) + '</div>'
						ELSE  '<div class="Healthy">' + LEFT(CONVERT(varchar,LastTranBackup,113),17) + '</div>' END as XML) td,
					FullDays td,
					DiffDays td,
					TranHours td,
					recovery_model_desc td
			FROM @backuplog bl
			LEFT JOIN sys.databases sdb on bl.DBName = sdb.name COLLATE SQL_Latin1_General_CP1_CI_AS AND bl.DBServer = @Server
			WHERE DBServer = @@SERVERNAME
			ORDER BY LastFullBackup,LastDiffBackup,LastTranBackup,DBName
			FOR XML RAW('tr'),ELEMENTS XSINIL
			)
			+ '</table>'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks_ErrorLog](@NumDays int)
AS
/* 
	Returns HTML for "ErrorLog.htm" attachment of DBA Checks Report
*/ 
SET NOCOUNT ON
CREATE TABLE #ErrorLog(
	LogDate datetime,
	ErrorSource nvarchar(max),
	ErrorMessage nvarchar(max)
)

CREATE TABLE #ErrorLogs(
	ID INT primary key not null,
	LogDate DateTime NOT NULL,
	LogFileSize bigint
)
DECLARE @MinDate datetime
SET @MinDate = CONVERT(datetime,CONVERT(varchar,DATEADD(d,-@NumDays,GetDate()),112),112)

--Get a list of available error logs
INSERT INTO #ErrorLogs(ID,LogDate,LogFileSize)
EXEC master.dbo.xp_enumerrorlogs

DECLARE @ErrorLogID int

DECLARE cErrorLogs CURSOR FOR
	SELECT ID
	FROM #ErrorLogs
	WHERE LogDate >= @MinDate

OPEN cErrorLogs
FETCH NEXT FROM cErrorLogs INTO @ErrorLogID
-- Read applicable error logs into the #errorlog table
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
	exec sp_readerrorlog @ErrorLogID
	FETCH NEXT FROM cErrorLogs INTO @ErrorLogID
END

CLOSE cErrorLogs
DEALLOCATE cErrorLogs

SELECT '<HTML>
<HEAD>
<style type="text/css">
table {
/*width:100%;*/
font:8pt tahoma,arial,sans-serif;
border-collapse:collapse;
}
th {
color:#FFFFFF;
font:bold 8pt tahoma,arial,sans-serif;
background-color:#204c7d;
padding-left:5px;
padding-right:5px;
}
td {
color:#000000;
font:8pt tahoma,arial,sans-serif;
border:1px solid #DCDCDC;
border-collapse:collapse;
}
.Warning {
background-color:#FFFF00; 
color:#2E2E2E;
}
.Critical {
background-color:#FF0000;
color:#FFFFFF;
}
.Healthy {
background-color:#458B00;
color:#FFFFFF;
}
</style>
</HEAD>
<BODY>
<table><tr><th>Log Date</th><th>Source</th><th>Message</th></tr>' + 
(SELECT CONVERT(varchar,LogDate,120) td,
	CAST('<div><![CDATA[' + ErrorSource + N']]></div>' as XML) td,
	CAST('<div' + 
		CASE WHEN (ErrorMessage LIKE '%error%' OR ErrorMessage LIKE '%exception%' 
					OR ErrorMessage LIKE '%stack dump%' OR ErrorMessage LIKE '%fail%') 
				AND ErrorMessage NOT LIKE '%DBCC%' THEN ' Class="Critical"' 
		WHEN ErrorMessage LIKE '%warning%' THEN ' Class="Warning"'
		ELSE '' END 
		+ '><![CDATA[' + ErrorMessage + N']]></div>' as XML) td
FROM #ErrorLog
WHERE LogDate >= @MinDate
/*	Remove any error log records that we are not interested in
	ammend the where clause as appropriate
*/
AND ErrorMessage NOT LIKE '%This is an informational message%'
AND ErrorMessage NOT LIKE 'Authentication mode is%'
AND ErrorMessage NOT LIKE 'System Manufacturer%'
AND ErrorMessage NOT LIKE 'All rights reserved.'
AND ErrorMessage NOT LIKE 'Server Process ID is%'
AND ErrorMessage NOT LIKE 'Starting up database%'
AND ErrorMessage NOT LIKE 'Registry startup parameters%'
AND ErrorMessage NOT LIKE '(c) 2005 Microsoft%'
AND ErrorMessage NOT LIKE 'Server is listening on%'
AND ErrorMessage NOT LIKE 'Server local connection provider is ready to accept connection on%'
AND ErrorMessage NOT LIKE 'Logging SQL Server messages in file%'
AND ErrorMessage <> 'Clearing tempdb database.'
AND ErrorMessage <> 'Using locked pages for buffer pool.'
AND ErrorMessage <> 'Service Broker manager has started.'
ORDER BY LogDate DESC
FOR XML RAW('tr'),ELEMENTS XSINIL)
+ '</table></HEAD></BODY>' as HTML

DROP TABLE #ErrorLog
DROP TABLE #ErrorLogs
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks_DBFiles](
	@IncludeDBs varchar(max)=NULL,
	@ExcludeDBs varchar(max)='master,model,msdb,tempdb',
	@WarningThresholdPCT int=90,
	@CriticalThresholdPCT int=95,
	@HTML varchar(max) output
)
AS
/* 
	Returns HTML for "Database Files" section of DBA Checks Report
*/ 
CREATE TABLE #FileStats(
	[db] sysname not null,
	[name] [sysname] not null,
	[file_group] [sysname] null,
	[physical_name] [nvarchar](260) NOT NULL,
	[type_desc] [nvarchar](60) NOT NULL,
	[size] [varchar](33) NOT NULL,
	[space_used] [varchar](33)  NULL,
	[free_space] [varchar](33)  NULL,
	[pct_used] [float]  NULL,
	[max_size] [varchar](33) NOT NULL,
	[growth] [varchar](33) NOT NULL
) 
DECLARE @IncludeXML XML
DECLARE @ExcludeXML XML
DECLARE @DB sysname

IF @IncludeDBs = ''
BEGIN
	SET @HTML = ''
	DROP TABLE #FileStats
	RETURN
END

SELECT @IncludeXML = '<a>' + REPLACE(@IncludeDBs,',','</a><a>') + '</a>'
SELECT @ExcludeXML = '<a>' + REPLACE(@ExcludeDBs,',','</a><a>') + '</a>'

DECLARE cDBs CURSOR FOR
			SELECT name FROM sys.databases
			WHERE (name IN(SELECT n.value('.','sysname')
						FROM @IncludeXML.nodes('/a') T(n))
						OR @IncludeXML IS NULL)
				AND (name NOT IN(SELECT n.value('.','sysname')
						FROM @ExcludeXML.nodes('/a') T(n))
						OR @ExcludeXML IS NULL)
			AND source_database_id IS NULL
			AND state = 0 --ONLINE
			ORDER BY name
			
OPEN cDBs
FETCH NEXT FROM cDBs INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @SQL nvarchar(max)
	SET @SQL =		 N'USE ' + QUOTENAME(@DB) + ';
					INSERT INTO #FileStats(db,name,file_group,physical_name,type_desc,size,space_used,free_space,[pct_used],max_size,growth)
					select DB_NAME() db,
					f.name,
					fg.name as file_group,
					f.physical_name,
					f.type_desc,
					CASE WHEN (f.size/128) < 1024 THEN CAST(f.size/128 as varchar) + '' MB'' 
						ELSE CAST(CAST(ROUND(f.size/(128*1024.0),1) as float) as varchar) + '' GB'' 
						END as size,
					CASE WHEN FILEPROPERTY(f.name,''spaceused'')/128 < 1024 THEN CAST(FILEPROPERTY(f.name,''spaceused'')/128 as varchar) + '' MB''
						ELSE CAST(CAST(ROUND(FILEPROPERTY(f.name,''spaceused'')/(128*1024.0),1) as float) as varchar) + '' GB'' 
						END space_used,
					CASE WHEN (f.size - FILEPROPERTY(f.name,''spaceused''))/128 < 1024 THEN CAST((f.size - FILEPROPERTY(f.name,''spaceused''))/128 as varchar) + '' MB''
						ELSE CAST(CAST(ROUND((f.size - FILEPROPERTY(f.name,''spaceused''))/(128*1024.0),1) as float) as varchar) + '' GB''
						END free_space,
					ROUND((FILEPROPERTY(f.name,''spaceused''))/CAST(size as float)*100,2) as [pct_used],
					CASE WHEN f.max_size =-1 THEN ''unlimited'' 
						WHEN f.max_size/128 < 1024 THEN CAST(f.max_size/128 as varchar) + '' MB'' 
						ELSE CAST(f.max_size/(128*1024) as varchar) + '' GB''
						END as max_size,
					CASE WHEN f.is_percent_growth=1 THEN CAST(f.growth as varchar) + ''%''
						WHEN f.growth = 0 THEN ''none''
						WHEN f.growth/128 < 1024 THEN CAST(f.growth/128 as varchar) + '' MB'' 
						ELSE CAST(CAST(ROUND(f.growth/(128*1024.0),1) as float) as varchar) + '' GB''
						END growth
					from sys.database_files f
					LEFT JOIN sys.filegroups fg on f.data_space_id = fg.data_space_id
					where f.type_desc <> ''FULLTEXT'''
	exec sp_executesql @SQL	
					
	FETCH NEXT FROM cDBs INTO @DB
END
CLOSE cDBs
DEALLOCATE cDBs

SELECT @HTML = '<h2>Database Files</h2><table>' + 
		(SELECT 'Database' th,
		'Name' th,
		'File Group' th,
		'File Path' th,
		'Type' th,
		'Size' th,
		'Used' th,
		'Free' th,
		'Used %' th,
		'Max Size' th,
		'Growth' th
		FOR XML RAW('tr'),ELEMENTS ) +		
		(SELECT db td,
					name td,
					file_group td,
					physical_name td,
					type_desc td,
					size td,
					space_used td,
					free_space td,
					CAST(CASE WHEN pct_used > @CriticalThresholdPCT 
						THEN '<div class="Critical">' + CAST(pct_used as varchar) + '</div>'
						WHEN pct_used > @WarningThresholdPCT  
						THEN '<div class="Warning">' + CAST(pct_used as varchar) + '</div>'
						ELSE '<div class="Healthy">' + CAST(pct_used as varchar) + '</div>'
						END as XML) td,
					max_size td,
					CAST(CASE WHEN growth='none' THEN '<div class="Warning">' + growth + '</div>'
					ELSE growth END as XML) td
				FROM #FileStats
				ORDER BY db,type_desc DESC,file_group,name
				FOR XML RAW('tr'),ELEMENTS XSINIL) + '</table>'
				
DROP TABLE #FileStats
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks_JobStats](@NumDays int,@HTML nvarchar(max) out)
AS
/* 
	Returns HTML for "Agent Jobs Stats in the last 'X' days" section of DBA Checks Report
*/ 
SET ANSI_WARNINGS OFF
DECLARE @FromDate char(8)
SET @FromDate = CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112);

WITH nextRun as (
	SELECT js.job_id, 
		MAX(CONVERT(datetime,CONVERT(CHAR(8), NULLIF(next_run_date,0), 112) 
			+ ' ' 
			+ STUFF(STUFF(RIGHT('000000' 
			+ CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':') )
			) as next_run_time
	FROM msdb..sysjobschedules js
	GROUP BY js.job_id
),
lastRun as (
	SELECT jh.job_id,CONVERT(datetime,CONVERT(CHAR(8), run_date, 112) 
		+ ' ' 
		+ STUFF(STUFF(RIGHT('000000' 
		+ CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':') ) as last_run_time,
		run_status as last_run_status,
		CAST(message as nvarchar(max)) as last_result,
		ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) rnum
	FROM msdb..sysjobhistory jh
	WHERE run_status IN(0,1,3) --Succeeded/Failed/Cancelled
)
,JobStats AS (
	select name,
			MAX(enabled) enabled,
			SUM(CASE WHEN run_status = 1 THEN 1 ELSE 0 END) as SucceededCount,
			SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END) as FailedCount,
			SUM(CASE WHEN run_status = 3 THEN 1 ELSE 0 END) as CancelledCount,
			MAX(last_run_time) last_run_time,
			MAX(next_run_time) next_run_time,
			MAX(last_run_status) last_run_status,
			COALESCE(MAX(last_result),'Unknown') last_result
	from msdb..sysjobs j
	LEFT JOIN msdb..sysjobhistory jh ON j.job_id = jh.job_id AND jh.run_date >= @FromDate and jh.step_id = 0
	LEFT JOIN nextrun ON j.job_id = nextrun.job_id
	LEFT JOIN lastRun ON j.job_id = lastRun.job_id AND rnum=1
	GROUP BY name
)
SELECT @HTML =N'<h2>Agent Job Stats in the last ' + CAST(@NumDays as varchar) + N' days</h2>
	<table>' +
	(SELECT 'Name' th,
	'Enabled' th,
	'Succeeded' th,
	'Failed' th,
	'Cancelled' th,
	'Last Run Time' th,
	'Next Run Time' th,
	'Last Result' th
	FOR XML RAW('tr'),ELEMENTS) 
	+ (SELECT name td,
			CAST(CASE WHEN enabled = 1 THEN N'<div class="Healthy">Yes</div>'
					ELSE N'<div class="Warning">No</div>' END as XML) td,
			CAST(CASE WHEN SucceededCount = 0 THEN  N'<div class="Warning">'
					ELSE N'<div>' END
					+ CAST(SucceededCount as varchar) + '</div>' as XML) td,
			CAST(CASE WHEN FailedCount >0 THEN  N'<div class="Critical">'
					ELSE N'<div class="Healthy">' END
					+ CAST(FailedCount as varchar) + N'</div>' as XML) td,
			CAST(CASE WHEN CancelledCount >0 THEN  N'<div class="Critical">'
					ELSE N'<div class="Healthy">' END
					+ CAST(CancelledCount as varchar) + N'</div>' as XML) td,
			LEFT(CONVERT(varchar,last_run_time,13),17) td,
			LEFT(CONVERT(varchar,next_run_time,13),17) td,
			CAST(CASE WHEN last_run_status = 1 THEN N'<span class="Healthy"><![CDATA[' + last_result + N']]></span>' 
					ELSE N'<span class="Critical"><![CDATA[' + last_result + N']]></span>' END  AS XML)  td 
		FROM JobStats
		ORDER BY last_run_time DESC
		FOR XML RAW('tr'),ELEMENTS XSINIL
	) + N'</table>'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks_FailedAgentJobs](@HTML varchar(max) out,@NumDays int)
AS
/* 
	Returns HTML for "Failed jobs in the last 'X' days" section of DBA Checks Report
*/ 
DECLARE @FromDate char(8)
DECLARE @SucceededCount int
SET @FromDate = CONVERT(char(8),dateadd (day,-@NumDays, getdate()), 112)

IF EXISTS(	
	SELECT *
	FROM msdb..sysjobhistory jh
	JOIN msdb..sysjobs j ON jh.job_id = j.job_id
	WHERE jh.run_status IN(0,3) -- Failed/Cancelled
		AND jh.step_id <> 0
		AND	run_date >= @FromDate)
BEGIN
SET @HTML = '<h2>Failed Jobs in the last ' + CAST(@NumDays as varchar) + ' days</h2>
	<table>
	<tr>
	<th>Date</th>
	<th>Job Name</th>
	<th>Job Status</th>
	<th>Step ID</th>
	<th>Step Name</th>
	<th>Message</th>
	<th>Run Duration</th>
	</tr>'
	+
	(SELECT CONVERT(datetime,CAST(jh.run_date AS char(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'),101) AS td,
		j.name AS td,
		CAST(CASE jh.run_status WHEN 0 THEN '<div class="Critical">Failed</div>'
			WHEN 3 THEN '<div class="Warning">Cancelled</div>'
			ELSE NULL END as XML) as td,
		jh.step_id as td,
		jh.step_name as td,
		jh.message as td,
				RIGHT('00' +CAST(run_duration/10000 as varchar),2) + ':' +
				RIGHT('00' + CAST(run_duration/100%100 as varchar),2) + ':' +
				RIGHT('00' + CAST(run_duration%100 as varchar),2) as td
	FROM	msdb..sysjobhistory jh
	JOIN	msdb..sysjobs j
		ON jh.job_id = j.job_id
	WHERE	jh.run_status IN(0,3) -- Failed/Cancelled
		AND jh.step_id <> 0
		AND	run_date >= @FromDate
	ORDER BY CONVERT(datetime,CAST(jh.run_date AS char(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'),101) DESC
	FOR XML RAW('tr'),ELEMENTS XSINIL
	)
	+ '</table><br/>'
END
ELSE
BEGIN
	SET @HTML = '<h2>Failed Jobs in the last ' + CAST(@NumDays as varchar) + ' days</h2>
				<span class="Healthy">No failed jobs</span><br/>'	
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks_DiskDrives](@PCTFreeWarningThreshold int,@PCTFreeCriticalThreshold int,@HTML varchar(max) out)
AS
/* 
	Returns HTML for "Disk Drives" section of DBA Checks Report
*/ 
DECLARE @BytesToTB float,@BytesToGB float,@BytesToMB float
SELECT @BytesToTB =  1099511627776,@BytesToGB = 1073741824,@BytesToMB=1048576.0;

WITH DriveInfo AS (
	SELECT drive,
		volume_label,
		CASE WHEN total_size > @BytesToTB THEN CAST(ROUND(total_size /@BytesToTB,1) as varchar) + 'TB'
			WHEN total_size > @BytesToGB THEN CAST(ROUND(total_size /@BytesToGB,1) as varchar) + 'GB'
			ELSE CAST(ROUND(total_size / @BytesToMB,1) as varchar) + 'MB' END as Size,
		CASE WHEN free_space > @BytesToTB THEN CAST(ROUND(free_space /@BytesToTB,1) as varchar) + 'TB'
			WHEN free_space > @BytesToGB THEN CAST(ROUND(free_space /@BytesToGB,1) as varchar) + 'GB'
			ELSE CAST(ROUND(free_space / @BytesToMB,1) as varchar) + 'MB' END as Free,
		ROUND(free_space/cast(total_size as float)*100,2) as PercentFree
	FROM dbo.SQLIO_fnGetDrives()
	WHERE drive_type=3 --Fixed
)
SELECT @HTML = 
	'<h2>Disk Drives</h2>
	<table>' +
	(SELECT 'Drive' th,
			'Label' th,
			'Size' th,
			'Free' th,
			'Free %' th
	FOR XML RAW('tr'),ELEMENTS) 
	+
	(SELECT drive td,
			volume_label td,
			Size td,
			Free td,
			CAST(CASE WHEN PercentFree < @PCTFreeCriticalThreshold THEN '<div class="Critical">' + CAST(PercentFree as varchar) + '</div>'
			WHEN PercentFree < @PCTFreeWarningThreshold THEN '<div class="Warning">' + CAST(PercentFree as varchar) + '</div>'
			ELSE '<div class="Healthy">' + CAST(PercentFree as varchar) + '</div>' END as XML) td
	FROM DriveInfo 
	FOR XML RAW('tr'),ELEMENTS XSINIL)
	+ '</table>'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBAChecks](
	/* Agent Jobs related Params */
	@AgentJobsNumDays int=3, --Num of days to report failed jobs over
	/* Database Files related Params */
	@FileStatsIncludedDatabases varchar(max)=NULL, -- Comma sep list of databases to report filestats for. NULL=All, '' = None
	@FileStatsExcludedDatabases varchar(max)=NULL, -- Comma sep list of databases to report filestats for. NULL=No Exclusions
	@FileStatsPctUsedWarning int=90, -- Warn (Yellow) if free space in a database file is less than this threshold (Just for database specified in @FileStatsDatabases)
	@FileStatsPctUsedCritical int=95, -- Warn (Red) if free space in a database file is less than this threshold (Just for database specified in @FileStatsDatabases)
	/* Backup related Params */
	@DiffWarningThresholdDays int=3, -- Backup warning if no diff backup for "x" days
	@FullWarningThresholdDays int=7, -- Backup warning if no full backup for "x" days
	@TranWarningThresholdHours int=4, -- Backup warning if no tran backup for "x" hours
	/* Disc Drive related params*/
	@FreeDiskSpacePercentWarningThreshold int=15, -- Warn (Yellow) if free space is less than this threshold
	@FreeDiskSpacePercentCriticalThreshold int=10, -- Warn (Red) if free space is less than this threshold
	/* General related params */
	@UptimeCritical int = 1440, -- Critical/Red if system uptime (in minutes) is less than this value
	@UptimeWarning int = 2880, -- Warn/Yellow if system uptime (in minutes) is less than this value,
	/* Error Log Params */
	@ErrorLogDays int = 3,
	/* Email/Profile params */
	@Recipients nvarchar(max), -- Email list
	@MailProfile sysname=NULL
)
AS
/*	Created By David Wiseman
	Version: 1.0
	http://www.wisesoft.co.uk
	Generates a DBA Checks HTML email report
*/
SET NOCOUNT ON
DECLARE @AgentJobsHTML varchar(max)
DECLARE @AgentJobStatsHTML varchar(max)
DECLARE @FileStatsHTML varchar(max)
DECLARE @DisksHTML varchar(max)
DECLARE @BackupsHTML varchar(max)
DECLARE @HTML varchar(max)
DECLARE @Uptime varchar(max)

SELECT @Uptime = 
	CASE WHEN DATEDIFF(mi,create_date,GetDate()) < @UptimeCritical THEN '<span class="Critical">'
	WHEN DATEDIFF(mi,create_date,GetDate()) < @Uptimewarning THEN '<span class="Warning">'
	ELSE '<span class="Healthy">' END + 
	-- get system uptime
	COALESCE(NULLIF(CAST((DATEDIFF(mi,create_date,GetDate())/1440 ) as varchar),'0') + ' day(s), ','')
	+ COALESCE(NULLIF(CAST(((DATEDIFF(mi,create_date,GetDate())%1440)/60) as varchar),'0') + ' hour(s), ','')
	+ CAST((DATEDIFF(mi,create_date,GetDate())%60) as varchar) + 'min'
	--
	+ '</span>'
FROM sys.databases 
WHERE NAME='tempdb'

exec dbo.DBAChecks_FailedAgentJobs @HTML=@AgentJobsHTML out,@NumDays=@AgentJobsNumDays

exec dbo.DBAChecks_JobStats @HTML=@AgentJobStatsHTML out,@NumDays=@AgentJobsNumDays

exec dbo.DBAChecks_DBFiles 
	@IncludeDBs=@FileStatsIncludedDatabases,
	@ExcludeDBs=@FileStatsExcludedDatabases,
	@WarningThresholdPCT=@FileStatsPctUsedWarning,
	@CriticalThresholdPCT=@FileStatsPctUsedCritical,
	@HTML=@FileStatsHTML out

exec dbo.DBAChecks_DiskDrives @HTML=@DisksHTML out,@PCTFreeWarningThreshold=@FreeDiskSpacePercentWarningThreshold,@PCTFreeCriticalThreshold=@FreeDiskSpacePercentCriticalThreshold 

exec dbo.DBAChecks_Backups @HTML=@BackupsHTML OUT,@DiffWarningThresHoldDays=@DiffWarningThresHoldDays,
	@FullWarningThresholdDays=@FullWarningThresholdDays ,@TranWarningThresholdHours=@FullWarningThresholdDays

SET @HTML = 
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style type="text/css">
table {
font:8pt tahoma,arial,sans-serif;
}
th {
color:#FFFFFF;
font:bold 8pt tahoma,arial,sans-serif;
background-color:#204c7d;
padding-left:5px;
padding-right:5px;
}
td {
color:#000000;
font:8pt tahoma,arial,sans-serif;
border:1px solid #DCDCDC;
border-collapse:collapse;
padding-left:3px;
padding-right:3px;
}
.Warning {
background-color:#FFFF00; 
color:#2E2E2E;
}
.Critical {
background-color:#FF0000;
color:#FFFFFF;
}
.Healthy {
background-color:#458B00;
color:#FFFFFF;
}
h1 {
color:#FFFFFF;
font:bold 16pt arial,sans-serif;
background-color:#204c7d;
text-align:center;
}
h2 {
color:#204c7d;
font:bold 14pt arial,sans-serif;
}
h3 {
color:#204c7d;
font:bold 12pt arial,sans-serif;
}
body {
color:#000000;
font:8pt tahoma,arial,sans-serif;
margin:0px;
padding:0px;
}
</style>
</head>
<body>
<h1>DBA Checks Report for ' + @@SERVERNAME + '</h1>
<h2>General Health</h2>
<b>System Uptime (SQL Server): ' + @Uptime + '</b><br/>
<b>Version: </b>' + CAST(SERVERPROPERTY('productversion') as nvarchar(max)) + ' ' 
	+ CAST(SERVERPROPERTY ('productlevel') as nvarchar(max))
    + ' ' + CAST(SERVERPROPERTY ('edition') as nvarchar(max)) 
+ COALESCE(@DisksHTML,'<div class="Critical">Error collecting Disk Info</div>')
+ COALESCE(@BackupsHTML,'<div class="Critical">Error collecting Backup Info</div>')
+ COALESCE(@AgentJobStatsHTML,'<div class="Critical">Error collecting Agent Jobs Stats</div>')
+ COALESCE(@AgentJobsHTML,'<div class="Critical">Error collecting Agent Jobs Info</div>')
+ COALESCE(@FileStatsHTML,'<div class="Critical">Error collecting File Stats Info</div>')
+ '</body></html>'

declare @subject varchar(50)
set @subject = 'DBA Checks (' + @@SERVERNAME + ')'

DECLARE @ErrorLogSQL nvarchar(max)
DECLARE @ExecuteQueryDB sysname
SET @ErrorLogSQL = 'exec DBAChecks_ErrorLog @NumDays=' + CAST(@ErrorLogDays as varchar)
SET @ExecuteQueryDB = DB_NAME()

EXEC msdb.dbo.sp_send_dbmail
	@query=@ErrorLogSQL,
	@attach_query_result_as_file = 1,
	@query_attachment_filename = 'ErrorLog.htm',
	@query_result_header = 0,
	@query_no_truncate = 1,
	@query_result_width=32767,
	@recipients =@Recipients,
	@body = @HTML,
	@body_format ='HTML',
	@subject = @subject,
	@execute_query_database=@ExecuteQueryDB,
	@profile_name = @MailProfile
GO