VirtualObjectives

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 KiB)

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:

  • This script is intended for SQL Server 2005 and above.
  • You must already have Database Mail setup to support sp_send_dbmail.

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:


Removal:

  • Run the removal section at the bottom of the script.