Startup procedure to check SQL Server status when restarted

I’ve written a startup procedure to check SQL Server status when the SQL Server gets restarted. Most of the time as a DBA when a Server gets rebooted or SQL Services is restarted we need to make sure everything is fine in SQL server to make sure SQL server is running healthy. Some companies will have budget to buy a monitoring tool, so that all these things will be taken care by that tool. This small procedure will help the DBA’s to know the status of SQL Server in HTML format through mail when it’s restarted.

Applies to:

• SQL Server 2005
• SQL Server 2008

Pre-Requisites

• Xp_cmdshell procedure should be in enabled state
• A database mail profile should be configured as default profile (else you need to provide this profile information inside the procedure)

Script

This script will monitor the below parameters in SQL Server.

• SQL Server Uptime
• SQL Server Service Status
• SQL Server Agent Status
• Database(s) status
• Authentication mode

/*
Procedure Name : serverstartup
Written By : Vidhya Sagar
www.sql-articles.com
*/
USE master
GO
CREATE PROC serverstartup
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##sqlservice')
DROP TABLE ##sqlservice
CREATE TABLE ##sqlservice (details VARCHAR(100))
IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##agentservice')
DROP TABLE ##agentservice
CREATE TABLE ##agentservice (details VARCHAR(100))
WAITFOR DELAY '00:02'
DECLARE @sname VARCHAR(100), @starttime VARCHAR(30)
DECLARE @authmode VARCHAR(25), @subject VARCHAR(250)
DECLARE @insname VARCHAR(50),@agentname VARCHAR(50)
DECLARE @sqlstatus VARCHAR(100), @agentstatus VARCHAR(100)
DECLARE @dbstatus VARCHAR(100), @dbdetail VARCHAR(2000)
DECLARE @sctsql VARCHAR(200), @HTML VARCHAR(8000)
SELECT @sname = @@SERVERNAME
SELECT @authmode=case SERVERPROPERTY('IsIntegratedSecurityOnly') when 1 then 'Windows' else 'Mixed' end
SELECT @starttime=CONVERT(VARCHAR(30),create_date,109) from sys.databases where database_id=2
IF (serverproperty('InstanceName')) IS NOT NULL
BEGIN
SET @insname='mssql$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))
SET @agentname='sqlagent$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))
END
ELSE
BEGIN
SET @insname='mssqlserver'
SET @agentname='sqlserveragent'
END
SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@insname+' | FIND "STATE"'''
INSERT ##sqlservice
EXEC (@sctsql)
SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@agentname+' | FIND "STATE"'''
INSERT ##agentservice
EXEC (@sctsql)
IF EXISTS(SELECT 1 FROM ##sqlservice WHERE details LIKE '%RUNNING%')
SET @sqlstatus = 'Running'
ELSE
SET @sqlstatus = '<font color="red">Not Running</font>'
IF EXISTS(SELECT 1 FROM ##agentservice WHERE details LIKE '%RUNNING%')
SET @agentstatus = 'Running'
ELSE
SET @agentstatus = '<font color="red">Not Running</font>'
IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc<>'ONLINE')
BEGIN
SET @dbstatus= '<font color="red">Some of the database(s) are offline</font>'
SELECT @dbdetail = '<table border="1"><tr><th>Database Name</th><th>Database Status</th></tr><tr>'
SELECT @dbdetail = @dbdetail + '<td  align="Center">' + name + '</td><td  align="Center">'+state_desc+'</td></tr></table>' FROM sys.databases WHERE state_desc<>'ONLINE'
END
ELSE
BEGIN
SET @dbdetail = ''
SET @dbstatus='All databases are online'
END
SET @subject=@sname+' : SQL Server is restarted. Please Check'
SET @HTML='<h3>'+@sname+'</h3><br>'+'<table border="1"><tr><th>SQL Server Startup time</th><th>SQL Server Service</th><th>SQL Agent Service</th> <th>Database(s) Status</th><th>Authentication Mode</th></tr><tr><td align="Center">'+
@starttime+'</td><td align="Center">'+@sqlstatus+'</td><td align="Center">'+@agentstatus+'</td><td align="Center">'+@dbstatus+'</td><td align="Center">'+@authmode+'</td></tr></table><br><br>'+@dbdetail
EXEC msdb.dbo.sp_send_dbmail
@recipients ='kvs1983@indiamvps.net',
@subject=@subject,
@body =@HTML,
@body_format ='HTML'
GO
---Enabling startup parameter for that procedure
EXEC sp_procoption 'serverstartup','startup',True
GO

In the script above there are two parameters that need to be taken care by the implementer.

• WAITFOR – I’ve used this command since this is the time for the database to get into steady state i.e. roll back and roll forward time. If the database is in huge size then the roll back or roll forward will take some time, you need to adjust this value based on the time its taking. By default I’ve set this value to 2 min.
• Mail Recipients – You need to provide the email address to whom the alert mail need to be delivered. In the script above I’ve used my hotmail ID. Please change this to your mail ID. If you don’t have a default mail profile, then use @profile_name parameter in the mail procedure to use the correct mail profile.

Testing

Let’s test the script developed. Since the script is marked for startup when ever SQL Server is restarted this procedure will run and provide the health check status for SQL Server to the mail recipients given in the script.

• When Everything is normal and running fine.

• In this case I’ve stopped SQL Agent service, lets check the alert mail. You can find SQL Agent status as not running.

• Now lets make a database to emergency mode. I’ve created a new database named EMERGENCY_TEST and made it to emergency mode. From the mail below you can check that you can get the db details too.

I’m sharing this procedure since some of the DBA’s might use this procedure to simplify their process.

Your comments are suggestions are always welcomed to improve me better.