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