Automatically Running a Process When SQL Server or SQL Agent Starts
September 30, 2005
Have you ever had a need to run a query or a process as soon as SQL Server starts? Or run some set of tasks when SQL Server Agent Starts? Possibly you want to run a cleanup routine, a copy process or have some task started each time SQL server or SQL Agent is started. Well if this is the case then this article will discuss a couple of options you might consider using to accomplish automatically running your process.
Executing a Stored Procedure When SQL Server Starts up
SQL Server has the capability to start a Stored Procedure (SP) when it starts up. This is done by setting the autoexecution option on your SP. To do this the SP must first exist. After creating your SP you can then use the system SP "sp_procoption" to set the autoexecution option for your SP. Once the autoexecution option is turned on for your SP, it will start every time SQL Server is started. This system SP is also used to turn off autoexecution.
The sp_procoption system SP uses the following syntax to turn on the autoexecution option:
sp_procoption @ProcName = 'YourSP', @OptionName = 'startup', @OptionValue = 'on'
Here you can see the "sp_procoption" SP is setting the autoexecution option by setting the @OptionName to "startup" and the @OptionValue to "on" for a @ProcName of "YourSP". After running the code above the SP "YourSP" will autoexecute whenever SQL Server starts. One of the requirements that SQL Server has is that the code for any SP that needs to autoexecute be stored in the master database. Therefore you much first place your SP in the master database before executing the "sp_procoption" system SP.
If you want to stop your code from autoexecuting you will need to turn off the autoexecution option for your SP. The following code can be used to turn off autoexecution for a stored procedure:
sp_procoption @ProcName = 'YourSP', @OptionName = 'startup', @OptionValue = 'off'
Here I passed a @ProcName value of "YourSP", a @OptionValue of "off" and a @OptionName of "startup" to the "sp_option" system SP. This will turn off the autoexecution property for the SP "YourSP".
If you need to identify if an SP has been setup for autoexecution you can check the object properties. To do this you use the Meta Data OBJECTPROPERTY scaler function. By using this scaler function to check the "ExecIsStartup" property of an SP you can determine if the SP is set up for autoexecution. The OBJECTPROPERTY function will return a 1 if the autoexecution is set "on" for an SP or 0 (zero) if it is turned off. Here is some code that uses the OBJECTPROPERTY Meta Data scaler function to check to see if the object "usp_autostart" in the master database is setup for autoexecution.
use master go IF OBJECTPROPERTY ( object_id('usp_autostart'),'ExecIsStartup') = 1 print 'usp_autostart will be executed at startup'
When the above code is run, if autoexecution is set "on" then the message "usp_autostart will be executed at startup" will be displayed.
Starting a Process When SQL Server Agent Starts Up
If you have a need to run one or more processes every time SQL Server Agent starts then SQL Server agent scheduling can accomplish that. A SQL Server Agent job can be automatically scheduled to run when SQL Server Agent starts up. To automatically run a job, when SQL Server Agent starts, create a new schedule and then select the "Start Automatically when SQL Server Agent Starts" radio button option for "schedule type". The screen below shows an example of a schedule named "AtStartup" that will run my job "Cleanup" every time SQL Server Agent Starts:
Any job that uses the schedule type of "Start Automatically when SQL Server Agent starts" will be run every time SQL Server Agent Starts. Remember SQL Server Agent can be stopped and restarted multiple times without bringing down SQL Server. So keep in mind that this method is not exactly the same as using the autoexecution option for a stored procedure.
As you can see, there are a couple of different ways to autmatically start a process when SQL Server or SQL Agent starts. Keep in mind that the SQL Agent method is slightly different than using the sp_procoption to automatically start a process. If you have a need to automatically start a process whenever SQL Server or SQL Agent starts, then the two options I discussed should be able to help you out.