Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (5)   |   Related Tips: More > Monitoring

Attend this free live MSSQLTips webcast

Leveraging Storage Spaces Direct for SQL Server High Availability

Thursday, July 19, 2018 - click here to learn more


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))+'\'[email protected]@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 '[email protected] +' (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 '[email protected]+' 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 '[email protected] 
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
			

Add Entries from the SQL Server Agent Log

 -- Agent log information
Insert into @SQL_Status_Report  (Information)
Select 'Agent log check on '[email protected]+' 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,[email protected]_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 '[email protected]+' 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>' [email protected]_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

new job

Add a Job Step

One job step is all that is required.

job step properties

Job Step Exit Status

advanced

Set Job Schedule

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

job properties

Example Email

Here is what a sample email looks like.

sql server status
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.


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, May 27, 2018 - 8:48:05 PM - Graham Okely Back To Top

Lee thanks, well spotted. I will get that adjusted. Graham


Friday, May 25, 2018 - 9:27:21 AM - Lee Back To Top

 

Thanks for taking the time to develop and share this procedure with us. Very useful. I had an issue with the section that retrieves data from the SQL Agent Log. Shoudn't the line:

where logdate>datediff(day,[email protected]_Days_Agent,getdate()) be something like:

where logdate> DATEADD(day,[email protected]_Days_Agent,GETDATE())

In my test SELECT datediff(day,-4,getdate()) returned 43247

Thanks again.

Lee


Thursday, May 24, 2018 - 10:53:56 AM - Cedrick Back To Top

 One of the best SQl Server articles that I have ever read and used!!! This is an invaluable tool for large environments.

Thanks for sharing!!!


Tuesday, May 15, 2018 - 8:34:00 PM - Vasanth Back To Top

 

 Great Article and hats off to your contribution to the SQL Server World, where me still Pupil.


Tuesday, May 15, 2018 - 6:21:55 AM - Srijaya Back To Top

 

 Great Article.   Thank you very much.


Learn more about SQL Server tools