Below is a script that you can use to send HTML Reports to your email just by using T-SQL. The example below retrieves the disk space usage of the DB Server then sends the report to my email.
-- Disk Space Usage
USE [master]
begin
set nocount on
/*******************************************************/
/* Enabling Ole Automation Procedures */
exec sp_configure 'show advanced options', 1
RECONFIGURE with override
exec sp_configure 'Ole Automation Procedures', 1
RECONFIGURE with override
/*******************************************************/
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives
(
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL
)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
/*******************************************************/
/* Disabling Ole Automation Procedures */
exec sp_configure 'show advanced options', 1
RECONFIGURE with override
exec sp_configure 'Ole Automation Procedures', 0
RECONFIGURE with override
/*******************************************************/
END
-- Check LogSpace Usage
DECLARE @SQLString nvarchar(500);
SET @SQLString = 'DBCC SQLPERF(LOGSPACE)'
CREATE TABLE #LogSpace
(
DatabaseName VARCHAR(MAX)
,LogSize INT
,LogSpaceUsedPct INT
,Status INT
)
INSERT INTO #LogSpace
EXEC sp_executesql @SQLString
GO
-- Check failed job
CREATE TABLE #FailedJobs
(
ServerName VARCHAR(50)
,JobName VARCHAR(50)
,JobStatus VARCHAR (20)
,LastRunStatus VARCHAR(30)
,LastRunDate DATETIME
)
INSERT INTO #FailedJobs
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity)
AND jh.run_status = 0
ORDER BY job_name,job_status
-- Check Error logs
declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();
-- Create the temporary table
CREATE TABLE #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(MAX))
-- Populate the temporary table
INSERT #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';
-- Create HTML Table for Disk Space Usage Report
DECLARE @tableHTML NVARCHAR(MAX);
SET @TableHTML = N'<H1>Disk Space Usage</H1>' + N'<table border="1">' + N'<tr>' +
N'<th bgcolor="#404040"><font color="#FFFFFF">drive</th><th bgcolor="#404040"><font color="#FFFFFF">Total(GB)</th>
<th bgcolor="#404040"><font color="#FFFFFF">Free(GB)</th><th bgcolor="#404040"><font color="#FFFFFF">Used(GB)</th>
<th bgcolor="#404040"><font color="#FFFFFF">PercentUsed</th>' + CAST ((
SELECT td = drive
,''
,td = cast(TotalSize/1024.0 as decimal(8,2))
,''
,td = cast(FreeSpace/1024.0 as decimal(8,2))
,''
,td = cast(TotalSize/1024.0-FreeSpace/1024.0 as decimal(8,2))
,''
,td = cast((TotalSize/1024.0-FreeSpace/1024.0)/(TotalSize/1024.0)*100 as decimal (8,2))
FROM
#drives
ORDER BY drive
FOR XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
-- Create HTML Table for Log Space Used
DECLARE @tableHTML_LogSpaceUsage NVARCHAR(MAX);
SET @tableHTML_LogSpaceUsage = N'<H1>Log Space Used</H1>' + N'<table border="1">' + N'<tr>' +
N'<th bgcolor="#404040"><font color="#FFFFFF">DatabaseName</th><th bgcolor="#404040"><font color="#FFFFFF">LogSize(MB)</th><th bgcolor="#404040"><font color="#FFFFFF">LogSpaceUsedPct</th>' + CAST ((
SELECT td = DatabaseName
,''
,td = LogSize
,''
,td = LogSpaceUsedPct
FROM
#LogSpace
FOR XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
-- Create HTML Table for Failed Jobs
DECLARE @tableHTML_FailedJobs NVARCHAR(MAX);
SET @tableHTML_FailedJobs = N'<H1>Failed Jobs</H1>' + N'<table border="1">' + N'<tr>' +
N'<th bgcolor="#404040"><font color="#FFFFFF">ServerName</th><th bgcolor="#404040"><font color="#FFFFFF">JobName</th><th bgcolor="#404040"><font color="#FFFFFF">JobStatus</th>
<th bgcolor="#404040"><font color="#FFFFFF">LastRunStatus</th><th bgcolor="#404040"><font color="#FFFFFF">LastRunDate</th>' + CAST ((
SELECT td = ServerName
,''
,td = JobName
,''
,td = JobStatus
,''
,td = LastRunStatus
,''
,td = LastRunDate
FROM
#FailedJobs
FOR XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
-- Create HTML Table for Error Log
DECLARE @tableHTML_ErrorLog NVARCHAR(MAX);
SET @tableHTML_ErrorLog = N'<H1>Error Log</H1>' + N'<table border="1">' + N'<tr>' +
N'<th bgcolor="#404040"><font color="#FFFFFF">LogDate</th><th bgcolor="#404040"><font color="#FFFFFF">Message</th>' + CAST ((
SELECT td = LogDate
,''
,td = Message
FROM
#ErrorLog
WHERE
(Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'
ORDER BY
logdate DESC
FOR XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
-- Send Email
DECLARE @AllTables NVARCHAR(MAX);
IF @tableHTML IS NOT NULL
SET @AllTables = @tableHTML
IF @tableHTML_LogSpaceUsage IS NOT NULL
SET @AllTables = @AllTables + @tableHTML_LogSpaceUsage
IF @tableHTML_FailedJobs IS NOT NULL
SET @AllTables = @AllTables + @tableHTML_FailedJobs
IF @tableHTML_ErrorLog IS NOT NULL
SET @AllTables = @AllTables + @tableHTML_ErrorLog
EXEC msdb.dbo.sp_send_dbmail @body = @AllTables
,@body_format = 'HTML'
,@profile_name = N'DBA'
,@recipients = N'ricky.valencia@optimalplus.com'
,@Subject = N'Database Health Check Report - ASEKH'
DROP TABLE #FailedJobs
DROP TABLE #LogSpace
DROP TABLE #drives
DROP TABLE #ErrorLog
You may put this script in SQL job.
Use this script as a template for whatever report you may think about.
Cheers!
