As a DBA it may be a challenging task to gather some system level or at business level some specific or few data needs to be check at SQL Server startup level. SQL Server provides this feature where we can execute stored procedure when SQL server instance starts and as per requirement we can analyze the data. For same first check system configuration, the option ‘scan for startup procs’ should be set 1.
Steps are as below,
— Step 1 – Lets check Where ‘show advanced options’ option is configured or not, if not, first configure it as
sp_configure ‘show advanced options’ ,1
Reconfigure with override
Go
— Step 2 – Configure ‘scan for startup procs’ option as
sp_configure ‘scan for startup procs’ ,1
Reconfigure with override
Go
From above syntax, server is now enabled to execute stored procedure at instance startup time.
As we know, when SQL Server starts, it first scans the registry to check the startup parameter values, during this it’s find the master database files and make the master database online. Now, Master Database is online, and startup procedure should be in Master Database and that SP will be execute from Master DB only. (Here is a restriction – Our SP should be in Master DB).
The sp_procoption stored procedure is used to execute the SP when SQL Server service starts as,
EXEC sp_procoption ‘procedure name’, ‘startup’, ‘true’
We can turn-off the Stored procedure execution at SQL server instance startup using below query.
EXEC sp_procoption ‘procedure name’, ‘startup’, ‘false’
Please comment on this topic.
WikiDBA 