USE master;
GO
ALTER PROC uspSQLStartupCheck
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: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;
DECLARE @agent NVARCHAR(512);
SELECT @agent = COALESCE(N'SQLAgent$'+CONVERT( SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServerAgent');
INSERT INTO ##agentservice
EXEC master.dbo.xp_servicecontrol
'QueryState',
@agent;
DECLARE @sql NVARCHAR(512);
SELECT @sql = COALESCE(N'MSSQL$'+CONVERT( SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServer');
INSERT INTO ##sqlservice
EXEC master.dbo.xp_servicecontrol
'QueryState',
@sql;
IF EXISTS
(
SELECT 1
FROM ##sqlservice
WHERE details LIKE '%Running%'
)
SET @sqlstatus = 'Running';
ELSE
select @sqlstatus = details from ##sqlservice
IF EXISTS
(
SELECT 1
FROM ##agentservice
WHERE details LIKE '%Running%'
)
SET @agentstatus = 'Running';
ELSE
select @agentstatus = details from ##agentservice
IF EXISTS
(
SELECT 1
FROM sys.databases
WHERE state_desc <> 'ONLINE'
)
BEGIN
SET @dbstatus = 'Some of the database(s) are offline';
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 = 'CRITICAL ALERT: Page ICS SQL DBA to check server ' + convert(varchar(128),@@servername) + ', its rebooted on ' + @starttime + '. SQL: ' + @sqlstatus + + ' Agent: ' + @agentstatus + ' and ' + @dbstatus
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;
if exists (
SELECT 1
FROM sys.databases d, ##agentservice a, ##sqlservice s
WHERE d.state_desc <> 'ONLINE' or a.details not like '%running%' or s.details not like '%running%'
)
begin
/* ... problem occurs ... */
exec xp_logevent 60000,@subject,error
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'yahoo@yahoo.com',
@from_address = 'yahoo@yahoo.com',
@subject = @subject,
@body = @HTML,
@query = ' SELECT SysJobs.name JOB_NAME
,SysJobSteps.step_name STEP_NAME
,Job.run_status
,Job.message
,Job.ExecDate
,Job.run_duration
,Job.server
FROM (SELECT Instance.instance_id
,DBSysJobHistory.job_id
,DBSysJobHistory.step_id
,DBSysJobHistory.sql_message_id
,DBSysJobHistory.sql_severity
,DBSysJobHistory.message
,(CASE DBSysJobHistory.run_status
WHEN 0 THEN ''Failed''
WHEN 1 THEN ''Succeeded''
WHEN 2 THEN ''Retry''
WHEN 3 THEN ''Canceled''
WHEN 4 THEN ''In progress''
END) as run_status
,((SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 5, 2) + ''/''
+ SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 7, 2) + ''/''
+ SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 1, 4) + '' ''
+ SUBSTRING((REPLICATE(''0'',6-LEN(CAST(DBSysJobHistory.run_time AS varchar)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 1, 2) + '':''
+ SUBSTRING((REPLICATE(''0'',6-LEN(CAST(DBSysJobHistory.run_time AS VARCHAR)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 3, 2) + '':''
+ SUBSTRING((REPLICATE(''0'',6-LEN(CAST(DBSysJobHistory.run_time as varchar)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 5, 2))) AS ExecDate
,DBSysJobHistory.run_duration
,DBSysJobHistory.retries_attempted
,DBSysJobHistory.server
FROM msdb.dbo.sysjobhistory DBSysJobHistory
JOIN (SELECT DBSysJobHistory.job_id
,DBSysJobHistory.step_id
,MAX(DBSysJobHistory.instance_id) as instance_id
FROM msdb.dbo.sysjobhistory DBSysJobHistory
GROUP BY DBSysJobHistory.job_id
,DBSysJobHistory.step_id
) AS Instance ON DBSysJobHistory.instance_id = Instance.instance_id
WHERE DBSysJobHistory.run_status <> 1 and DBSysJobHistory.run_time > DATEADD(HH,-1,GETDATE())
) AS Job
JOIN msdb.dbo.sysjobs SysJobs
ON (Job.job_id = SysJobs.job_id)
JOIN msdb.dbo.sysjobsteps SysJobSteps
ON (Job.job_id = SysJobSteps.job_id AND Job.step_id = SysJobSteps.step_id)',
@attach_query_result_as_file = 1,
@body_format = 'HTML'
END
GO
---Enabling startup parameter for that procedure
EXEC sp_procoption
'uspSQLStartupCheck',
'startup',
True;
GO
