Sunday, April 28, 2019

How to find all startup stored procedures?

I upgraded an instance from v2008 to v2014 over the weekend.  Fortunately, everything went well, but this was one of the prerequisites:

  • Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time might block the upgrade process.

So I ask myself -- how do I find all startup procs?  Very simple.  Run this query to identify what procedures are configured to run at startup:

  -- find procs that run at startup
  SELECT name,create_date,modify_date
  FROM sys.procedures
  WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1


And run this to tell the system to run it at startup, or not:

  -- edit to run at startup, or not
  EXEC sp_procoption
   @procname = 'usp_MssqlServiceStart',@optionname= 'startup',@optionvalue = 'off'



As I said, very simple.  Take a look at this for more details on sys.procedures:

And even this for more details on the SQL Server upgrade:


No comments:

Post a Comment