Automatically Running Process When SQL Server starts

Have you ever had a need to run a query or a process as soon as SQL Server starts? Possibly you want to run a cleanup or copy routine each time SQL server is started. Well if so then here are a couple of options you might considering using to accomplish just that.

The first method is to create a stored procedure and then set the procedure for autoexecution. When a stored procedure has been set for autoexecution the store procedure runs every time SQL Server starts. You use the sp_procoption system stored procedure to set a stored procedure for autoexecution. This system stored procedure is also used to turn off autoexecution.

The sp_procoption uses the following syntax to turn on a store procedure for autoexecution:

sp_procoption @ProcName = ‘YourSP',
@OptionName = 'startup',
@OptionValue = 'on'

The above code will set the stored procedure “YourSP” to autoexecute whenever SQL Server starts. The code for “YourSP” will need to be stored in the master database, since only SP’s in the master database can be set to autoexecute.

The following command is used to turn off autoexeuction for a stored procedure.

sp_procoption @ProcName = 'YourSP',
@OptionName = 'startup',
@OptionValue = 'off'