Category: T-SQL

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!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter