Feb 9, 2011

Get Notification as soon as SQL Services restarted

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 = 'rahul.vairagi068@gmail.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

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.