How to Use SQL Server Startup Procedures

Last Updated on August 29, 2020 by John Morehouse

Startup procedures in Microsoft SQL Server allows for pre-determined stored procedures to automatically execute when SQL Server starts.  However, it’s not all fun and games.  Let’s take a look!

After reviewing some recent audit results for a couple of our SQL Servers, I noted the audit checked for start up procedures.  This caught my attention as I was not fully aware of what those were.  Naturally I decided to investigate further.

Startup procedures automatically execute whenever SQL Server is started.  Where would you use this?  One example is if you had an application that required the use of a global temporary table, you could use a startup procedure to create it.  This would allow the table to be immediately accessible to any application that requires it.

Another possibility is that you could “warm up” the cache so that data is already cached when an application needs it.  This would help prevent that initial I/O hit the first time the query is called, thus potentially improving initial performance.

Are there gotchas?

Just like with anything within SQL server, there are some things that you need to be aware of:

  • Each startup procedure consumes a worker thread while executing
  • The stored procedure must live in the Master database
  • Only system administrators can set a procedure as a startup procedure (this is a good thing actually).  The SA account must also own the procedure
  • It cannot have any input or output parameters
  • The procedure executes with the same permissions as the sysadmin role
  • It should not return any result sets. It gets execute by the instance so there is not anywhere for the results to go

How do we configure startup procedures?

So how do you configure this wizardry, you ask?  There are two methods to enable this.

  1. You can use sp_configure
  2. sp_procoption

Let’s walk through both of them.

Using sp_configure is simple.  If we look at sys.configurations, we can see whether or not it is already enabled.  It is disabled by default.

From the above, we can see that it is disabled for this particular instance.  This is an advanced configuration setting so we have to ensure that ‘show advanced options‘ is enabled.  Once that is completed, we can then enable the scan for startup procedures.

Hold the phone.  Even after setting it to be enabled, it STILL shows disabled.

This is another gotcha.  You have to restart the SQL Server services in order for the configuration change to take effect.  So, plan accordingly if you have a need to use this feature.

As a side note, if you start SQL Server with the -f flag (which is minimal configuration) the startup procedures will not be executed.  You can also use trace flag 4022 as another option to skip them upon startup.

The other option is to use a system procedure sp_procoption.  The sole purpose of this system procedure is to set or clear a procedure for automatic execution.  Just like using sp_configure, this procedure is fairly simple as well:

To enable a procedure:

exec sp_procoption @ProcName = 'dbo.LoadAllOfTheThingsIntoCache', @OptionName = 'startup', @OptionValue = 'on';

To disable a procedure:

exec sp_procoption @ProcName = 'dbo.LoadAllOfTheThingsIntoCache', @OptionValue = 'off';

As a side note, if the 'scan for startup procs' is not enabled when you use sp_procoption, it will automatically enable it for you.

Just remember that any procedure that is configured to be executed upon startup has to live in the Master database.

Is it used in the real world?

Have I ever used start up procedures?  No, I have not.  I believe that it’s an edge case usage, however it is always good to know what options you have available to you.  I do think that using it to warm up the cache for highly transaction systems might be useful.

With that being said, here’s the obligatory warning:

This was informational only.  Do NOT go wild and create 100+ startup procedures on your SQL Servers.  Doing so will cause problems.  As we have learned from Indiana Jones, choose wisely.  

If anybody is using start up procedures, I would love to hear about your experience in the comments!

Enjoy!


© 2017 – 2020, John Morehouse. All rights reserved.

8 Replies to “How to Use SQL Server Startup Procedures”

  1. I use startup procedures to load basic trace flags. I do this so that the startup procedure can be centrally deployed, and I can drop it in source control to audit the standardized configuration.

  2. Oooo…that’s an interesting notion. Didn’t think about going down that path with them. Awesome stuff David! Thanks for sharing!

  3. We use a startup procedure to write startup and shutdown times to a table, so we can track our downtime across the environment and then aggregate the times to figure out our total downtime. Last year was less than 30 mins across 100+ instances. This year sucks thanks to a DC move.

  4. Today , when I was checking new Cluster Configuration I found that Dinamc AX 2012 use startup Stored Procedure to grant permissions to Logins.

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.