Systems Engineering and RDBMS

Startup Procedures in SQL Server

Posted by decipherinfosys on August 4, 2007

The definition of a startup procedure is that it is a procedure that is marked for automatic execution at the time of the start of the SQL Server Service after the master database goes through the recovery phase. So, how can this be useful? This can be useful under two circumstances:

1) If you want to create temporary objects in the TEMPDB database, then you can put that code in a stored procedure and mark it for start-up. That way, those objects will always exist in the TEMPDB database.

2) If you want a stored procedure to run as a background process or want that stored procedure to do some quick auditing of the configuration settings (though this can be done as a scheduled job as well).

This requires sysadmin privileges. The errors generated by the stored procedure will get logged into the SQL Server Error log but make it a point not to return any result sets from this stored procedure since this is being executed by the engine itself and not by an application. So, it should not have any input or output parameters either.

So, how can one mark a stored procedure to be a startup procedure? Using sp_procoption, one can do that using this syntax:

use master
go
exec sp_procoption @procname = ‘proc1’, @optionname = ‘startup’, @optionvalue = ‘on’
go

And even if stored procedure(s) are marked with this option, one can still disable this by using the configuration option: “scan for startup procs”. The default value is 0 which means do not scan for start up procedures. If this value is marked as 1, then SQL Server will automatically scan and run the stored procedures that are marked for start up.

And in order to help you find out stored procedures that are marked for automatic start-up execution, one can make use of the OBJECTPROPERTY() function and use the property: “ExecIsStartup”. If the value returned is 1, it means that it is a start up procedure, if the value returned is 0, it means that it is not. Example:

select name
from sys.objects
where type = ‘p’
and OBJECTPROPERTY(object_id, ‘ExecIsStartup’) = 1

Sorry, the comment form is closed at this time.

 
%d bloggers like this: