-- File name : usp_sql_server_status_check_HTML.sql Use [DBA] -- Name your administrative database here GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO begin try drop procedure [dbo].[usp_sql_server_status_check_HTML] end try begin catch end catch go Create procedure [dbo].[usp_sql_server_status_check_HTML] @Test nvarchar(3)= null as BEGIN set nocount on -- File name : usp_sql_server_status_check_HTML.sql -- Author : Graham Okely B App Sc (IT) -- Reference : https://www.mssqltips.com/sqlserverauthor/106/graham-okely/ Declare @Reference nvarchar(128) = 'https://www.mssqltips.com/sqlserverauthor/106/graham-okely/' Declare @Report_Name nvarchar(128) = 'SQL Server Status Report' -- Just in case @@Servername is null Declare @Instance nvarchar(128) = ( Select isnull(@@Servername,cast(SERVERPROPERTY('MachineName') as nvarchar(128))+'\'+@@servicename) ) -- Get this many days from the SQL Agent log Declare @Log_Days_Agent int = 4 -- Build a table for the report Declare @SQL_Status_Report table ( Line_Number int NOT NULL identity(1,1), Information nvarchar(max) ) -- Main title of the report Insert into @SQL_Status_Report (Information) Select 'SQL Server Status Check Report on '+ @Instance +' at '+cast(getdate() as nvarchar(28)) Insert into @SQL_Status_Report (Information) Select 'On node : '+ cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar(1024)) Insert into @SQL_Status_Report (Information) Select @@version -- This line makes a blank row in the report Insert into @SQL_Status_Report (Information) select '' -- Get the last restart date and time from sqlserver_start_time Insert into @SQL_Status_report SELECT 'Start time from DMV sqlserver_start_time ' + cast(sqlserver_start_time as nvarchar (28)) FROM sys.dm_os_sys_info -- Disk Drive Space Insert into @SQL_Status_Report (Information) Select 'Drive space on '+@Instance +' (Lowest space free first)' declare @drives table( drive nvarchar(1), MbFree int) insert into @drives exec xp_fixeddrives Insert into @SQL_Status_Report (Information) select drive+ ' has ' + cast (MbFree/1000 as nvarchar(20))+' GB Free' from @drives Order by MbFree asc -- Show least amount of space first -- Users added in the last X days Declare @DaysBack int=7 Insert into @SQL_Status_Report (Information) Select 'Users added in last '+ cast(@DaysBack as nvarchar(12))+' days' Insert into @SQL_Status_Report (Information) Select name+' '+type_desc+ ' '+ cast(create_date as nvarchar(28))+' ' + cast(datediff(day,create_date,getdate()) as nvarchar(12)) + ' days ago' From sys.server_principals Where type_desc in ('WINDOWS_LOGIN','WINDOWS_GROUP','SQL_LOGIN') and datediff(day,create_date,getdate())<@DaysBack -- Gather summary of databases using sp_helpdb Declare @sp_helpdb_results table( [db_name] nvarchar(256), [db_size] nvarchar(25), [owner] nvarchar(128), [db_id] int, [created_data] datetime, [status] nvarchar(max), [compatability] int) INSERT INTO @sp_helpdb_results EXEC sp_helpdb -- Flag databases with an unknown status insert into @sp_helpdb_results( [db_name],[owner],[db_size]) SELECT name,'Database Status Unknown' COLLATE database_default,0 FROM sys.sysdatabases Where [name] COLLATE database_default not in (Select [db_name] COLLATE database_default from @sp_helpdb_results) -- Remove " MB" UPDATE @sp_helpdb_results SET [db_size] = replace([db_size],' MB','') Delete from @sp_helpdb_results Where [db_size]='0' -- Report summary of databases using sp_helpdb Insert into @SQL_Status_Report (Information) Select @Instance+' has ' + cast(count(*) as nvarchar(8)) + ' databases with ' + cast(cast(sum(cast(replace([db_size],' MB','') as float)) as int)/1000 as nvarchar(20)) + ' GB of data' FROM @sp_helpdb_results -- Database sizes Insert into @SQL_Status_Report (Information) Select 'Largest database on '+@Instance+' in MB' Insert into @SQL_Status_Report (Information) Select top 1 [db_name] + ' ' + convert(nvarchar(10),round(convert(numeric,ltrim(replace([db_size],' Mb',''))),0)) From @sp_helpdb_results Order by [db_size] desc -- Oldest backup Insert into @SQL_Status_Report (Information) Select 'Oldest full database backup on '+@Instance Insert into @SQL_Status_Report (Information) Select top 1 left(database_name,30)+' '+COALESCE(Convert(varchar(10), MAX(backup_finish_date), 121),'Not Yet Taken') From msdb..backupset Where database_name not in ( 'tempdb' ) and type = 'D' Group by database_name Order by MAX(backup_finish_date) asc -- Agent log information Insert into @SQL_Status_Report (Information) Select 'Agent log check on '+@Instance+' Last ' +cast( @Log_Days_Agent as nvarchar(12))+ ' days' Declare @SqlAgenterrorLog table(logdate datetime, [ProcessInfo] varchar(29), errortext varchar(max)) insert into @SqlAgenterrorLog exec sys.xp_readerrorlog 0,2 -- Report Insert into @SQL_Status_Report (Information) Select DISTINCT cast(logdate as nvarchar(28))+' '+[ProcessInfo]+' '+left(errortext,300) from @SqlAgenterrorLog where logdate>datediff(day,-@Log_Days_Agent,getdate()) order by 1 desc -- Server log last 20 rows Insert into @SQL_Status_Report (Information) Select 'Sql Server log check on '+@Instance+' top 20 rows' Declare @SqlerrorLog table(logdate datetime, [ProcessInfo] varchar(29), errortext varchar(max)) insert into @SqlerrorLog exec sys.xp_readerrorlog Insert into @SQL_Status_Report (Information) Select top 20 cast(logdate as nvarchar(28))+' '+[ProcessInfo]+' '+left(errortext,300) from @SqlerrorLog order by 1 desc -- Report Footer Insert into @SQL_Status_Report (Information) Select 'End of the ' + @Report_Name + ' on ' + @Instance + ' at ' + cast(getdate() as nvarchar(28)) Insert into @SQL_Status_Report (Information) Select 'For more tips see : ' + @Reference END -- Prepare email Declare @xml nvarchar(MAX) Declare @body NVARCHAR(MAX) SET @xml = cast(( select ltrim(Information) as'td' FROM @SQL_Status_Report ORDER BY Line_Number FOR XML PATH('tr'), ELEMENTS ) AS nvarchar(MAX)) Declare @Subject_Line nvarchar(128) = 'SQL Server Status Report from ' + @Instance SET @body ='' SET @body = @body + @xml +'

' +@Subject_Line+'

' If ( @Test='Yes' ) BEGIN Set @Subject_Line = @Subject_Line + ' Test Mode' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'Your.email@data.com', -- replace with your email address @subject = @Subject_Line; print @body END Else BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'monitoring_team@data.com', -- replace with the monitoring email address @subject = @Subject_Line; END