Startup Stored Procedures in SQL Server: Uses and Risks

Startup stored procedures run every time SQL Server starts, which makes them useful and easy to forget. This post covers good uses, the risks, and how to list and remove the ones already on a server. The setup steps are in a separate post titled Run a Stored Procedure at Startup in SQL Server.

What the Rules Are

A startup procedure lives in master, is owned by dbo, and takes no parameters. It runs with sysadmin permissions. SQL Server starts it after master is recovered, while other databases can still be recovering. Those rules decide everything below.

The permissions mean its code deserves the same care as any privileged script. The recovery order means it can’t assume that a user database is online. And the missing parameters mean every setting is fixed in the code.

Good Uses for Startup Stored Procedures

The jobs that fit are small and local to the instance. Recording each start in a history table is the classic one. Clearing a work table that must start empty after a restart is another. A third is a flag row that an application reads to learn about a restart. It can then rebuild its own caches.

The jobs that don’t fit need the outside world. The network, Database Mail or a linked server can be unavailable at that moment. A startup procedure also has no one to report a failure to. Wrap the body in TRY and CATCH, and write any error to a table you can read later.

Long loops are the other trap. A procedure that archives old rows in an endless WHILE loop never returns. It keeps a worker busy for the life of the instance. That job belongs in a SQL Server Agent job or a service. On Express, which has no Agent, keep any startup loop short and bounded.

Wait for the Database, but Not Forever

Most startup procedures write to a user database. That database can still be recovering when the procedure runs. So the procedure has to wait. It must also give up. This demo shows both. It logs the outcome in a history table, either way.

SET NOCOUNT ON;
IF DB_ID(N'StartupProcLogDemo') IS NULL CREATE DATABASE StartupProcLogDemo;
GO
USE StartupProcLogDemo;
GO
DROP TABLE IF EXISTS dbo.StartupLog;
CREATE TABLE dbo.StartupLog (
    LogID    int IDENTITY(1,1) PRIMARY KEY,
    LoggedAt datetime2(0) NOT NULL DEFAULT SYSDATETIME(),
    Outcome  nvarchar(100) NOT NULL
);

The procedure counts its tries. It loops while the target database isn’t online and the count is below the limit. A missing database counts as not online. After the loop it logs one row, whatever happened.

CREATE OR ALTER PROCEDURE dbo.LogStartWhenReady
    @TargetDatabase sysname,
    @MaxTries       int     = 24,
    @Delay          char(8) = '00:00:05'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @tries int = 0;
    WHILE ISNULL(DATABASEPROPERTYEX(@TargetDatabase, 'Status'), N'MISSING') <> N'ONLINE' AND @tries < @MaxTries
    BEGIN
        WAITFOR DELAY @Delay;
        SET @tries += 1;
    END;
    IF DATABASEPROPERTYEX(@TargetDatabase, 'Status') = N'ONLINE'
        INSERT INTO dbo.StartupLog (Outcome) VALUES (CONCAT(N'Logged after ', @tries, N' waits'));
    ELSE
        INSERT INTO dbo.StartupLog (Outcome) VALUES (CONCAT(N'Gave up after ', @tries, N' waits: ', @TargetDatabase, N' not online'));
END;

The first call uses the online demo database. The second names a database that doesn’t exist. It allows 3 tries with a 1 second delay, so the test ends in 3 seconds.

EXEC dbo.LogStartWhenReady @TargetDatabase = N'StartupProcLogDemo';
EXEC dbo.LogStartWhenReady @TargetDatabase = N'NoSuchDatabaseHere', @MaxTries = 3, @Delay = '00:00:01';
SELECT LogID, Outcome FROM dbo.StartupLog ORDER BY LogID;

SSMS cuts the second Outcome value at the column width. The full text ends with not online. The second call returned instead of waiting forever, and it left a row that tells you why. A real startup procedure can’t take parameters. In master, declare three local variables instead: the database name, 24 tries and a 5 second delay. That is a wait of at most two minutes.

The Risks

Errors in startup stored procedures go to the error log, and nobody sees them in a query window. So keep the code short and log what it does. The procedure runs at every start, including a restart after a failover on a cluster. The flagged procedures live in master, so rebuilding master loses them. Keep their scripts somewhere safe.

When the instance misbehaves at startup, suspect them. Trace flag 4022 makes SQL Server start without running any of them. That separates a startup procedure problem from everything else.

List the Startup Stored Procedures on a Server

Old servers collect startup stored procedures. Nobody remembers who added them. This query lists every flagged procedure in master.

SELECT p.name, p.create_date
FROM master.sys.procedures AS p
WHERE p.is_auto_executed = 1;

On the demo server the list is empty. Then check the server option that goes with the feature.

SELECT name, value, value_in_use, is_advanced
FROM sys.configurations
WHERE name = N'scan for startup procs';

With no flagged procedure, the option reads 0. In the setup post’s own test, flagging the first procedure set the option to 1 by itself. The value in use stayed 0 until the next restart. Removing the last flag set it back to 0. So the option follows the flags. You don’t set it by hand.

Remove One

Read the procedure’s code first and script it, so you can put it back. Then turn the flag off and drop the procedure. These statements change master. They need a test server and sysadmin rights, and the demo doesn’t run them. Replace the name with the one from the list.

USE master;
GO
EXEC sp_procoption N'dbo.NameFromTheList', 'startup', 'off';
DROP PROCEDURE dbo.NameFromTheList;

Run the list query again. It should return no row for that name. If the procedure was the last one, the option reads 0 again after the change.

Is a Startup Procedure the Right Tool?

You could argue that a startup procedure is the simplest way to start a background task. On Express it can be the only built-in way. Elsewhere, an Agent job gives you a history, an owner and a place to see failures. Use a startup procedure for the one thing an Agent job can’t do: act at the moment the instance starts.

What to Remember

Keep startup stored procedures small, bounded and logged. Check the list on every server you inherit. Run the cleanup script when you finish with the demo.

USE master;
GO
IF DB_ID(N'StartupProcLogDemo') IS NOT NULL
BEGIN
    ALTER DATABASE StartupProcLogDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE StartupProcLogDemo;
END;

If you also followed the setup post on a test server, undo its changes in master as well. This block changes master, so the demo doesn’t run it. The names are those of the setup post.

USE master;
GO
IF OBJECT_ID(N'dbo.LogServerStart') IS NOT NULL
BEGIN
    EXEC sp_procoption N'dbo.LogServerStart', 'startup', 'off';
    DROP PROCEDURE dbo.LogServerStart;
END;

A startup procedure is not magic, it is a promise to run before anyone is watching.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.