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.
USE master GO --Creating history table CREATE TABLE SQLRestartHistory (id int identity(1,1), InstanceName varchar(100), StartupTime datetime default getdate()) GO --Creating a procedure to handle my startup stuff CREATE PROC usp_sqlREstartHistory AS BEGIN --Maintains history INSERT INTO master..SQLRestartHistory (InstanceName, StartupTime) VALUES (@@servicename,GETDATE()) --Other tasks you want to include at startup like email notification etc CREATE TABLE ##MyGlobalTable (id int identity(1,1), Name varchar(100)) END GO --Configuring the sp for autoexecution EXEC sp_procoption 'usp_sqlREstartHistory', 'startup', 'true'
--List of stored procedures set for auto execution SELECT name, type_desc, create_date, modify_date FROM sys.procedures WHERE is_auto_executed = 1
|usp_sqlREstartHistory||SQL_STORED_PROCEDURE||6/14/2015 16:03||6/14/2015 16:21|
--Disable the proc from autoexecution EXEC sp_procoption 'usp_sqlREstartHistory', 'startup', 'false' GO -- Lab cleanup DROP PROC usp_sqlREstartHistory GO DROP TABLE SQLRestartHistory