Startup Procedure Tip

Have you ever tried to launch some SQL Server startup procedures only to find that it doesn’t return any row when it should?  Then, when you run the same startup procedure directly in Query Analyzer using the same connection credential, it returns the results as expected.

You may be hitting a little know aspect of startup procedures with regards to the number of connections they use.  If you read about the CREATE PROCEDURE statement in SQL Server Books On-Line (BOL), you’ll find:

There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.

In fact, the BOL really means worker threads (as in the max worker threads setting in sp_configure) rather than connections (as in spids).  BTW, Linchi Shea, a SQL Server MVP working at Merrill Lynch, tested this behavior and confirmed the anomaly.

Cheers,

-Kevin