MS SQL: Execute Database Request after Server Start

Question

I need to execute a request (Stored Procedure) to MS SQL Server after Windows restart, i.e. as soon as the database is accessible.

How can I do that? Is there a possibility to register a database event that fires after startup or a system event after a service started (here: SQL Server Service)?

Answer 1

You can use a Startup Stored Procedure or schedule a SQL Agent with a Schedule type of "Start automatically when SQL Server Agent Starts".

Answer 2

There is a system stored procedure named sp_procoption that does exactly that. See

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-procoption-transact-sql?view=sql-server-ver15

Syntax is fairly straight forward. If you have a procedure named 'my_stored_proc', the syntax is:

EXEC sp_procoption @ProcName = N'my_stored_proc'   
, @OptionName = 'startup'   
, @OptionValue = 'on';   

The only value for @OptionName is 'startup', and the only possible values for @OptionValue are 'on' and 'off.'