If you have a need for a SQL Server maintenance process to spawn during SQL Server startup, you should look into the sp_procoption system stored procedure. This procedure will allow you to define application-specific procedures to execute when the SQL Server Service starts.

If you have an application that needs to "poll" for data, you could use this autostart approach to establish a continuous loop, which acts upon the data in some predefined manner. The sp_procoption procedure can also set other procedures to execute upon starting SQL Server.

sp_procoption [[@ProcName =] 'procedure']

[,[@OptionName =] 'option']

[,[@OptionValue =] 'value']

EXECUTE sp_procoption pPollForData, startup, true

Would cause the pPollForData procedure to be flagged for auto startup.

EXECUTE sp_procoption pPollForData, startup

Would display the information regarding pPollForData.

Option Status Procedure Name Owner Name Proc Type

------------- ------------------ ---------- ---------

True pPollForData dbo P

EXECUTE sp_procoption pPollForData, startup, false

Would reverse the pPollForData procedure flagged for auto startup.

Option Status Procedure Name Owner Name Proc Type

------------- ------------------ ---------- ---------

False pPollForData dbo P

Note that you can have more than one procedure flagged for autoexecution during startup. However, the autoexecuted procedure(s) must be owned by the database owner (dbo) and must be located in the master database. You can reference/call procedures in other databases from within the autostart procedure (pPollForData).