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)