SQL Server Restart Notification using sp_procoption

If you ever wanted to be notified by email when your SQL Servers are restarted, here is a script which will do just that using sp_procoption which sets stored procedures for autoexecution.

Download SQL Server Restart Notification using sp_procoption (Size 2 KB)

What it does:

This script sets up a stored procedure in the master database. sp_procoption is used to configure the stored procedure to run when the SQL server is started.
Upon the SQL server starting, the stored procedure is called and it checks to see if tempdb is recently created. If so, then it issues an email with the date and time the server was started.

Alternative scripts which uses the Agent Manager instead.

Requirements:

Installation:

  1. Edit the script.
  2. Change the line @recipients = 'someone@domainname.com' to be the email address you want to send the alerts to. Use semicolons to separate multiple recipients.
  3. Run the script by the steps indicated in the script.

Script:

-- SQL Server Restart Notification 2005 using sp_procoption.sql
-- (C) Copyright 2010 John Buoro, VirtualObjectives
-- http://www.VirtualObjectives.com.au

-- This script is intended for SQL Server 2005 and above.
-- This script sets up a stored procedure in the master database. 
-- sp_procoption is used to configure the stored procedure to run when the server is started.
-- Upon the server starting, the stored procedure checks to see if tempdb is recently created.
-- If so, the script then issues an email with the date and time the server was started.
-- Checking tempdb creation time is in a way redundant but is a good way to double check things.

-- STEP 1: Change the @recipients email address below in STEP 2.
-- Use semicolons to separate multiple recipients.

-- STEP 2: Create stored procedure in the master database.
USE [master]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SQLServerRestartNotification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SQLServerRestartNotification]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE usp_SQLServerRestartNotification
AS
BEGIN
-- Detect if SQL Server was restarted.
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 Server <b>' + @@SERVERNAME + '</b> was restarted on <b>' + @SQLServerStarted + '</b>'
    EXEC @rc = msdb.dbo.sp_send_dbmail
        @recipients = 'someone@domainname.com',
        @importance = 'high',
        @subject = 'SQL Server Restart Notification',
        @body_format = 'html',
        @body = @msg,
        @exclude_query_output = 1
    IF @rc = 1 RAISERROR('sp_send_dbmail Failed', 16, 1)
END
END
GO

-- STEP 3: Add usp_SQLServerRestartNotification to startup.
sp_procoption @ProcName = 'usp_SQLServerRestartNotification', 
              @OptionName = 'startup', 
              @OptionValue = 'on'

-- STEP 4: Check it's there.
IF OBJECTPROPERTY ( object_id('usp_SQLServerRestartNotification'),'ExecIsStartup') = 1
   PRINT 'usp_SQLServerRestartNotification will be executed at startup.'

-- STEP 5: List all stored procedures set to startup.
SELECT ROUTINE_NAME, OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') as execisstartup
FROM MASTER.INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE' and
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

-- REMOVAL: Run the following to remove the notification from the server.
/*
-- Remove usp_SQLServerRestartNotification from startup.
sp_procoption @ProcName = 'usp_SQLServerRestartNotification', 
              @OptionName = 'startup', 
              @OptionValue = 'off'

-- Remove stored procedure usp_SQLServerRestartNotification from master.
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SQLServerRestartNotification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SQLServerRestartNotification]
GO
*/

Removal: