Execute stored procedure on startup – sp_procoption – SQL Server

Recently a friend of mine asked me that how can he record his SQL Server service restart history in a database table, also would like to get some kind of notification email when the instance comes online after a server reboot or after the service recycle without any 3rd party application installed? I could have suggested him to write a vb script or powershell script and automated the task, but fortunately we have an inbuilt feature available in SQL Server to execute stored procedure on startup. Is not that cool?

sp_procoption is a system procedure which allows us to set a stored procedure for auto execution when an instance of SQL Server is started either by server reboot or just service recycle. Create your own customized stored procedure on master db that you want to execute on startup and then set it up for auto execution.

sp_procoption-sql-server

id InstanceName StartupTime
1 SQLINDIA 2015-06-14 16:34:47.213
name type_desc create_date modify_date
usp_sqlREstartHistory SQL_STORED_PROCEDURE 6/14/2015 16:03 6/14/2015 16:21

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: