Quick Health Check After Restart of SQL Server Instance
As a DBA one of the roles is to verify, check and monitor critical or important SQL Server instances after restart or cluster failover or reboot of the server. Sometimes it is possible that there are some problems after the server is rebooted or SQL instance is restarted or failover is performed, so a quick check should verify to make sure nothing is wrong or broken. Also, sometimes there are unplanned server reboots or cluster failovers or SQL instance restarts, so keeping the items which are to be monitored after the restarts will be good.
Also, in many environments there are automated monitoring tools which detect any problems, but for critical servers, it is always good to manually have a quick health check to make sure there are no issues. This is because sometimes automated monitoring tools may not start properly or does not detect the problems due to bugs or other issues, so checking critical servers manually is always a good choice. Below are some of the important items which you may want to consider checking after the restart of SQL Server instance, either planned or unplanned.
– Verify the Database Status to make sure all the databases have successfully recovered and shows online status.
– On cluster servers, if you have configured Preferred owners for any SQL Server instance, make sure the SQL Server instances are running on node they are expected to run on.
– There may be jobs which might be running during the time of restart, they would have failed in middly, so it is good to check for any failed jobs and restart them if necessary.
– If there is database mirroring setup, monitor the Mirroring Status to make sure it caught up and is not in suspended state.
– Starting SQL Server 2012, if AlwaysON AG is setup, then make sure Availability Group Status is synchronized and there are no failures and databases or AG not in resolving state.
– If replication has been setup, verify and make sure Replication is functioning fine and all jobs are running as expected.
– Check SQL Server errorlog and SQL Agent log log make sure there are no errors.
– Check Event Viewer application and system logs to make sure there are no errors.
There may be other items to check as well depending on the features used or enabled on the SQL Server instance like Audits, CDC, MDW, Policy management, etc, so list down the features implemented and prepare the list specific to the servers.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings