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
