Get Notification as soon as SQL Services are restarted

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev | Posted on 17-09-2010

1

Being production DBA and responsible for a dozen to 100+ SQL Servers running in your organization or datacenter, Sometimes, you must be wondering that how you can make sure you are fully aware what is going when you are not at work. There is an easy way to stay in touch with your SQL Server(s), you can create a SQL job on the server, relax and hope you never get such alarm. yup, as soon as SQL Services will restart, the first thing its going to do is send you an email for restart notification.

 

DECLARE @UpTimeDays int
DECLARE @SQLSvcStarted varchar(20)
DECLARE @rc int
DECLARE @msg varchar(1000)
SET @UpTimeDays=(select DateDiff(D, CrDate, GetDate()) from master..sysdatabases where name='tempdb')
IF @UpTimeDays = 0
BEGIN
SET @SQLSvcStarted = (select convert(varchar(20), CrDate, 113) 
from master..sysdatabases where name = 'tempdb')
SET @msg='The SQL Services on <b>'+@@SERVERNAME+'</b> was restarted on <b>'+@SQLSvcStarted +'</b>'
EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfileName',
@recipients = 'dk@sqlknowledge.com',
@importance = 'high',
@subject = 'SQL Server Restart Notification!',
@body_format = 'html',
@body = @msg,
@exclude_query_output = 1
IF @rc = 1 RAISERROR('xp_smtp_sendmail Failed', 16, 1)
END
© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com