Automate a SQL Server Status Report on Server Restart or Scheduled

By: Graham Okely | Updated: 2018-05-15 | Comments (14) | Related: > Monitoring

Problem

Do you know when a SQL Server instance restarts? When you manage many SQL Server instances you may not know when one restarts, so having an automated report emailed to you could be helpful to get an idea what's going on for that instance.

Solution

This tip shows you how to build a SQL Server status report and have it emailed to you when an instance restarts or on a set schedule.

Prerequisites

One prerequisite is that you enable email on your instance. See tip 1100 by Greg Robidoux for that.
Next we will be using tip 2347 by Bru Medishetty on HTML formatting in emails.
The last restart time tip is 2501 by Greg Robidoux.
Lastly we will build a SQL Server job and if you need help on the SQL Server job system see tip 4848 by Rick Dobson.

Step 1 - Build a stored procedure to collect important information

Tip : Use a test mode to email yourself then turn off test mode to email the usual monitoring group.

The call for running the stored procedure in test mode is:

Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test=’Yes’

The call for running the stored procedure when test mode off is:

Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test=’No’

The stored procedure has many components to check for various SQL Server conditions.

This is the first part of the stored procedure.

-- 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) )

Step 2 - What detail do we want to see

Some basics like the instance name. Then the active node the instance is on. That helps when managing clusters and does not hurt when it is a standalone instance.

Here are various code snippets that can be added to the stored procedure.

Add SQL Server Version Information

 -- 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 ''

Add SQL Server Restart Time

 -- 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

Add Space Free on Each Disk Linked to the Instance

 -- 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

Add Users Added in the Last X Days

Just so we know who was added.

 -- 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

Add Total Number of Databases and Total Space

That gives me an idea of what is hosted.

 -- 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

Add Largest Database

That helps me know what the system mainly hosts.

-- 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

Add Date of Oldest Backup

That lets me know if the backup system is OK.

 -- 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 database_name in (select distinct name FROM master..sysdatabases)
and type = 'D' 
Group by database_name
Order by MAX(backup_finish_date) asc

Add Entries from the SQL Server Agent Log

 -- 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>dateadd(day,-@Log_Days_Agent,getdate()) 
order by 1 desc

Add Entries from the SQL Server Error Log

 -- 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

Add Your Own Checks

You can add anything else you would like to add. Just follow the template from one of the above sections.

Add Report Footer

This is needed to close out the report as well as add END to the bottom of the stored procedure.

 -- 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

Step 3 - Email Formatting and Delivery

Finally prepare and email the report.

You can add this section to email the report. Just change the values below to match your database mail profile and the email addresses that you want to get the report.

 -- 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 ='<html><body><table border = 1 width="80%"><th><H3>' +@Subject_Line+'</H3></th>'    
 
SET @body = @body + @xml +'</table></body></html>'
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 = '[email protected]', -- 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 = '[email protected]', -- replace with the monitoring email address 
   @subject = @Subject_Line; 
END

Step 4 - Build

Copy all the parts you want to use from above into one file and build the stored procedure. Click here for the complete stored procedure.

Step 5 - Test

We can test the stored procedure using the following options.

 -- Send the email to the tester
Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test=’Yes’

-- Send the email to the monitoring group
Exec [DBA].[dbo].[usp_sql_server_status_check_HTML] @Test=’No’

-- Send the email to the monitoring group
Exec [DBA].[dbo].[usp_sql_server_status_check_HTML]

Step 6 - Schedule

To schedule the stored procedure to run, we can create a SQL Server Agent Job.

Deploy by Creating a SQL Server Agent Job

Add a Job Step

One job step is all that is required.

Job Step Exit Status

Set Job Schedule

Let’s make the job run on start-up and the third Tuesday of each month.

Example Email

Here is what a sample email looks like.

Next Steps

Read more tips on Database Mail
Enable email on your instance. See tip 1100 by Greg Robidoux.
See tip 2347 by Bru Medishetty on HTML formatting in emails.
See this tip on the SQL Server job system tip 4848 by Rick Dobson.

About the author

Graham Okely is a SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-05-15

Build and Automate a SQL Server Status Report on Server Restart or Scheduled