SQL Server — Start-up Procedures

Today I want to discuss with you all a great feature that is available with SQL Server which is ‘scan for startup procs’. It’s an advanced configuration within SQL server and enables SQL server to scan for any procedures marked as start-up procs and run them.

What makes it so great?

You all must be thinking ok then why is so great then, we have seen start-up program options within our windows OS as well. I would say, it’s the usage that make it special.

We all must have been through a database environment where we don’t have any proper monitoring available and it might have multiple times that some XYZ server restarted on some day at some time. No one knows about it until some user for SQL server comes and tells us that “Hey this server is unstable, it was not available on that day”. I am pretty sure the first reply would have been that SQL is working fine, no issues here which you soon realized after looking at your error log that there was a SQL restart may be due to Server Shutdown or due to any other reason for that matters. Moreover, by the time you have realized that it restarted, you must have lost all server logs.

In such a situation this option can be very useful to you. You can create a procedure to notify or email you and set it as start-up proc. Now, this cannot stop the restart to happen but it can send you an email as soon as it comes up again. Obviously that is possible only if it ever comes back but with a no monitoring available this can be life savior.

How to do enable it?

Following is the extract from Microsoft Docs:

The value for this option can be set by using sp_configure; however, it will be set automatically if you use sp_procoption, which is used to mark or unmark automatically run stored procedures. 
When sp_procoption is used to mark the first stored procedure as an autoproc, this option is set automatically to a value of 1. When sp_procoption is used to unmark the last stored procedure as an autoproc, this option is automatically set to a value of 0. 
If you use sp_procoption to mark and unmark autoprocs, and if you always unmark autoprocs before dropping them, there is no need to set this option manually.

Script to set a proc to run automatically:

Using sp_procoption: Sets or clears a stored procedure for automatic execution. A stored procedure that is set to automatic execution runs every time an instance of SQL Server is started.

sp_procoption @ProcName = 'procedure'  , @OptionName = 'startup', @OptionValue = 'on/off' OR ‘true/false’

You can setup the scan for start-up procs option manually. This is pretty easy. It’s an advanced option in SQL server, so you just need to enable advanced options and then enable scan for start-up procs.

EXEC sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'scan for startup procs', 1 ;  
GO  
RECONFIGURE  
GO

Note: The server must be restarted before the setting can take effect.

Script to alert on SQL restart:

Brent Ozar have done this work for you and shared this great script that not only enables this option but also checks for database status and alerts database states on every SQL start-up. You can find it here:

https://github.com/BrentOzarULTD/BrentOzar.com-Demos/blob/master/T-SQL/sp_SendStartupMail.sql

https://www.brentozar.com/archive/2020/07/get-alerted-when-your-sql-server-restarts-with-sp_sendstartupmail/

This is not all, you may customize and use this option in any way that suits your requirements. It’s a great feature that makes a DBA life simpler.

Caution

Ensure that you don’t set up too many start-up procs. It will take time to run all those procedures and might be an unnecessary load on the server. Choose wisely.

Hope today’s learning was useful as well as fun.

Happy Learning folks!!