Tuesday 12 February 2013

Get Mail Alert when SQL Server Restarts

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