Using below query we can configure a alert mail whenever a SQL Server gets restarted
--- Checking startup stored procedures configuration status
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO
--- Create table in one of the User Database
CREATE TABLE ECSDBAMonitor.dbo.server_restart_info
(ServerName varchar(500),ReportTime datetime,LogDate datetime,LastText varchar(1000))
--- Create Procedure in Master Database
create PROCEDURE usp_server_restart_log
As
Begin
SET NOCOUNT ON
Create table #errorlog (logdate datetime,processinfo varchar(500),lasttext varchar(1000))
declare @startdate datetime,@enddate datetime
set @enddate=getdate()
set @startdate=dateadd(mi,-90,getdate())
--select @enddate,@startdate
insert #errorlog
Exec xp_readerrorlog 1,1,NULL,NULL,@startdate,@enddate,'desc'
declare @logdate datetime,@lasttext varchar(1000)
select top 1 @logdate=logdate,@lasttext=lasttext from #errorlog
--select @logdate,@lasttext
delete ECSDBAMonitor.dbo.server_restart_info where ReportTime<(getdate()-180)
insert into ECSDBAMonitor.dbo.server_restart_info values
(@@servername,getdate(),@logdate,@lasttext)
Drop table #errorlog
End
Go
--- Configuring startup stored procedures for the server
EXEC sp_procoption @ProcName = 'usp_server_restart_log'
, @OptionName = 'startup'
, @OptionValue = 'on';
--- Checking startup stored procedures configuration status
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO
--- Creating a Trigger on User Table with which we would be getting an alert mail when SQl Server
--- Restart and adds a row to user table. Chnage the Mail Id's, Profile Information
--- as per the environment
create TRIGGER tr_server_restart_info_mail
ON server_restart_info
AFTER INSERT
AS
declare @servername varchar(500),@reporttime datetime,@logdate datetime,@lasttext varchar(1000)
select @servername=ServerName,@reporttime=ReportTime
,@logdate=LogDate,@lasttext=LastText
from inserted
declare @body varchar(2000)
select @body=N'<H1><font size="2" face="cambria">Hi Team,<br><br>'
select @body=@body +N' Below SQL Server was restarted at below mentioned time, please check if the server
was restarted unexpectedly. Please ignore the mail if the server was restarted for
maintenance activity<br><br></H1>'
Select @body = @body+
N'<H1><font size="2" face="cambria">SQL Server Restart Information</font></H1>' +
N'<table border="1">' +
N'<tr><th><font size="2" face="cambria">ServerName</th><th>
<font size="2" face="cambria">ReportTime</th><th>
<font size="2" face="cambria">LogDate</th><th>
<font size="2" face="cambria">LastText</th>'
select @body = @body + '<tr><th><font size="2" color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@servername),' ')+
'</font></th><th><font size="2" color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@reporttime),' ')+
'</font></th><th><font size="2" color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@logdate),' ')+
'</font></th><th><font size="2" color="forestgreen" face="cambria">'+isnull(convert(varchar(1000),@lasttext),' ')+
'</font></th>'
select @body = @body + '</tr></table>'
declare @subject varchar(500)
set @subject='SQL Server Restart Info of '+@servername
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'pawankalyan@power.com',
--@copy_recipients=' pawankalyan@power.com',
@subject =@subject,
@body =@body,
@profile_name='Mail Alerts',
@body_format = 'HTML' ;
Go
