How to automate post reboot verification of SQL Server.

Recently we have been facing an issue in one of our test servers. It is having new issue every time server gets rebooted, sometimes few databases go offline or would be stuck in middle of restore or SQL agent won’t come up and etc. Being a test server several people do rigorous testing’s on it and can’t blame anyone if they have modified any option for their testing purposes but at the end of the day everyone needs all the databases of the so called test server to be healthy and available all the times. So I have been searching for a way to automate the verification of stuff we need to in SQL Server every time it gets re-started.

Then I learnt that there is an option in SQL Server to execute stored procedure every time the SQL services get re-started.

By turning on the below option we can make any user defined SP to get executed automatically every time SQL services gets re-started. I have tested this on SQL Server 2005\2008\R2\2012\2014 versions successfully.

sp_procoption @ProcName = 'UserDefinedSP',               @OptionName = 'STARTUP',               @OptionValue = 'ON'

Note: We can turn this option ON for the SP’s that are in master database only. SP cannot contain INPUT or OUTPUT parameters.

I coined below simple SP which will verify status of all the databases after every time SQL services get re-started and sends mail notification.

Same way we can make use of this to verify different things of SQL Server.

Thanks!!