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?

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?

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:

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:

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

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

Happy Learning folks!!

--

--

Hi! I am Amit your DataGuy. Folks do call me ‘AD’ as well. I have worked over a decade with into multiple roles - developer, dba, data engineer, data architect.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Dataguy! - databare.com

Hi! I am Amit your DataGuy. Folks do call me ‘AD’ as well. I have worked over a decade with into multiple roles - developer, dba, data engineer, data architect.