Log Shipping – NORECOVERY vs. STANDBY mode

This is actually quite simple. There is 'startup' option that you can set to the procedure.

There are a few limitations though:

- sp must reside in the [master] database

- it's owner must be dbo

- it mustn't have any input or output parameters

Note that each stored procedure run at start up takes up one worker thread until finished. So if you want to run multiple sps at runtime and parallelism doesn't matter create one sp that executes all others.

sample code

USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
IF OBJECT_ID('spTest') IS NOT NULL
    DROP PROC spTest
GO
-- crate a test stored procedure
CREATE PROC spTest
AS
-- just create a sample database
EXEC('CREATE database db1')
GO
-- set it to run at sql server start-up
exec sp_procoption N'spTest', 'startup', 'on'