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

Comments (1)

On my production cluster, I use a feature of the SQL agent that can run a job on Service startup.
It is similar to this script
http://www.virtualobjectives.com.au/sqlserver/restart_notification.htm
or
http://www.databasejournal.com/features/mssql/article.php/3550601/Automatically-Running-a-Process-When-SQL-Server-or-SQL-Agent-Starts.htm

I get notified the instance sql agent comes up.
The secret sauce is on the schedule type of the job. Rather than running hourly or one time, change the job schedule type to be ‘RUN on SQL Agent Startup’

Write a comment

*

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com