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.
Same
way we can make use of this to verify different things of SQL Server.
Thanks!!
