SQL Server — Start-up Procedures

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.

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’
EXEC sp_configure 'show advanced options', 1 ;  
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO

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:

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.

--

--

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

Dataguy! - databare.com

11 Followers

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.