master Startup Procedures
Overview
A startup procedure runs as sysadmin, on its own thread, before the instance is finished coming up, with no session to report to.
One that blocks holds the instance in the state where it is running and refusing connections, and the only trace of it is a line in the error log that nobody reads because the server appears to be down.
There is usually none of these on an instance. When there is one, it is either replication’s or somebody’s, and knowing which is the whole job of this page.
Where to find it
Expand a server in the tree, expand the master database, then Master → Startup Procedures.
The startup parameters are here for the same reason
Rather than on a configuration page.
A trace flag set with -T in the service command line is on at every restart and does not appear in any configuration view.
DBCC TRACESTATUS shows it alongside flags somebody turned on by hand this morning, and the two behave completely differently the next time the service bounces.
The one parsing trap
The startup parameter values in the registry can carry a trailing NUL byte, and splitting on the separator without trimming it makes every flag after the last one parse as nothing at all.
This page trims it. A page that does not shows an instance with startup trace flags as an instance with none.
Reading the chart
One bar per thing that happens when this service starts, with the procedures first because those are the ones that can hold the instance in the state where it is running and refusing connections.
The bars draw the top thirteen of the twenty one. The grid holds them all.
Reading the grid
| Column | What it is |
|---|---|
| What | A startup procedure, a startup parameter, or a trace flag from the command line. |
| Name | The procedure name, the parameter, or the flag number. |
| What it does | For a procedure, the start of its definition. For a known trace flag or parameter, what it changes. |
| Where it comes from | Replication, SQL Server setup, or somebody. |
| Last changed | modify_date for a procedure. |
The toolbar
| Control | What it does |
|---|---|
| Service facts | Opens master Service Facts, which shares the registry read. |
| User objects | Opens master User Objects, since a hand written startup procedure is one. |
| Trace flags | Opens the Trace Flags report, which shows what is on now. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.procedures with is_auto_executed = 1 |
The startup procedures. |
sys.sql_modules |
Their definitions. |
sys.dm_server_registry |
The startup parameters, including -T flags, -e, -l and -d. |
DBCC TRACESTATUS |
Which flags are on now, for the comparison. |
Related reports
| Report | Why you would go there |
|---|---|
| Trace Flags | Which flags are on now, session and global, in full. |
| master Service Facts | The service account, start mode and error log path. |
| master User Objects | A hand written startup procedure is also a user object in master. |
| Error Log | The only place a blocking startup procedure leaves a trace. |
| master Backup and Rebuild Readiness | Whether the startup procedure is in a backup. |
Frequently asked questions
The instance starts and refuses connections for minutes. Look for a startup procedure. One that blocks holds the instance in exactly that state, and the error log is the only record.
A trace flag is on and I cannot find where it was set. If it is on this page it is on the service command line and survives every restart. If it is not, it was set with DBCC TRACEON and will be gone at the next bounce.
Which startup procedures are normal? Replication installs some. Setup installs none. Anything else was put there by a person, and the Where it comes from column says so.
How do I remove one? sp_procoption with @OptionValue = 'off'. The page does not run it, for the same reason the rest of the suite does not change anything.