SQLKnowledge.com

Day by day Experience of a Microsoft SQL Server production DBA & Consultant..

Get Notification as soon as SQL Services are restarted

Posted on | February 18, 2010 | No Comments

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 @SQLServerStarted 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 @SQLServerStarted = (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>’ + @SQLServerStarted + ‘</b>’

EXEC @rc = msdb.dbo.sp_send_dbmail

@profile_name = ‘SQLMailProfileName’,

@recipients = ‘test@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

Category: SQL DBA, Scripts
Tags: > > > > >

Comments

Leave a Reply