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.

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

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 (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – How to Get SQL Server Restart Notification?

  1. 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

    Like

  2. 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’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s