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:

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:

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.

Conclusion

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.