SQL SERVER – How to Get SQL Server Restart Notification?

Few days back my friend called me to know if there is any tool which can be used to get restart notification about SQL in their environment. I told that SQL Server can do it by itself with some configurations. He was happy and surprised to know that he need not spend any extra money.

In SQL Server, we can configure stored procedure(s) to run at start-up of SQL Server. This blog would give steps to achieve how to achieve it.

There are many situations where this feature can be used. Below are few.

  1. Logging SQL Server startup timings
  2. Modify data in some table during startup (i.e. table in tempdb)
  3. Sending notification about SQL start.

Step 1 – Enable ‘scan for startup procs’

This can be done either using T-SQL or User Interface of Management Studio.

EXEC sys.sp_configure N'Show Advanced Options', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'scan for startup procs', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Below is the interface to change the setting. We need to go to “Server” > “Properties” and use “Advanced” tab. “Scan for Startup Procs” is the parameter under “Miscellaneous” section as shown below.

SQL SERVER - How to Get SQL Server Restart Notification? 01_StartupProc_SSMS

We need to make value as “True” and hit OK.

Step 2 – Create stored procedure

It’s important to note that the procedure is executed after recovery is finished for ALL databases. Here is a sample stored procedure. You can use your own logic in the procedure.

CREATE PROCEDURE SQLStartupProc
AS
BEGIN
CREATE TABLE
##ThisTableShouldAlwaysExists (AnyColumn INT)
END

Step 3 – Set Procedure to run at startup

We need to use sp_procoption to mark the procedure to run at startup. Here is the code to let SQL know that this is startup proc.

sp_procoption 'SQLStartupProc', 'startup', 'true'

This can be used only for procedures in master database.

Msg 15398, Level 11, State 1, Procedure sp_procoption, Line 89
Only objects in the master database owned by dbo can have the startup setting changed.

We also need to remember that such procedure should not have any input/output parameter. Here is the error which would be raised.

Msg 15399, Level 11, State 1, Procedure sp_procoption, Line 107
Could not change startup option because this option is restricted to objects that have no parameters.

Verification

Here is the query to find which procedures is marked as startup procedures.

SELECT name FROM sys.objects
WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1

SQL SERVER - How to Get SQL Server Restart Notification? 02_StartupProc_SysObjects

Once this is done, I have restarted SQL instance and here is what we would see in SQL ERRORLOG

Launched startup procedure 'SQLStartupProc'.

This confirms that stored procedure is executed. You can also notice that this is done after all databases are recovered.

Recovery is complete. This is an informational message only. 
No user action is required.

After few days my friend again called me and asked – I want to turn this OFF?

Use comments section and post the answer for him.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – SQL Server High Availability Options – Notes from the Field #032
Next Post
SQLAuthority News – SQL Server 2012 Service Pack 2 is Available

Related Posts

11 Comments. Leave new

  • Hareesh Gottipati
    June 13, 2014 9:53 am

    Hello Pinal’s friend – here this one for you :)

    EXEC sys.sp_configure N’Show Advanced Options’, N’1′
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sys.sp_configure N’scan for startup procs’, N’0′
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    Reply
  • Hi,
    Turn off the setting just set scan for Startup procs property false.

    Reply
  • how can i get email alert once server restarted

    Reply
  • sp_procoption ‘SQLStartupProc’, ‘startup’, ‘false’

    Reply
  • Hello Pinal, Thank you for another informative and useful post.

    Hello Pinal’s friend, The answer depends on what you want. If you are trying to turn off startup procs completely, that has already been answered by other commenters. If you have other startup procs, but just want to undo having the new one you created run at startup:

    sp_procoption ‘SQLStartupProc’, ‘startup’, ‘false’

    Reply
  • Thanks Pinal for this blog – very useful and infrmative!! Awesome answer Dave Rael.

    Reply
  • Thanks Dave..I have created one procedure to send email by using SP_send_dbmail…so when we restarts the server it will send mail to me

    Reply
  • hi pinal ,how to get sql server shutdown notification

    Reply

Leave a Reply

Menu