SQL Server Morning Health Checks – UPDATED!!!

A while back I wrote an article about how I created a PowerShell script for a customer to help them with their morning routine of checking the health of each SQL Server in their environment. I have not worked with that customer in a good while, but I thought it would be useful to update the script with some additional checks.

The original script helps the DBA answer these questions:

1. What is the uptime of each SQL Server?
2. What is the status of each database?
3. What is the status of each Availability Group?
4. What is the backup status of each database?
5. What is the available disk space?
6. Are there any SQL Agent failed jobs in the last 24 hours?
7. What errors appeared in the SQL errorlog in the last 24 hours?

For the updated script, I have added checks to answer these questions:

1. What is the status of each SQL Server service?
2. What is the status of each Windows Cluster node?

The first check will simply query the sys.dm_server_services DMV to get the status of each service. If the startup mode is “Automatic” and the service is not “Running” then it results in a critical status; otherwise, the status is good.

The second check will query both the sys.dm_os_cluster_nodes and sys.dm_hadr_cluster_members DMVs to get the status of each cluster node. If a node is “Up”, then the status is good; otherwise, it reports a critical status.

The final changes were to fix a few bugs and other functional issues.

1. Changed the error trapping to continue processing even if a SQL Server is unresponsive.
2. Updated the SQL Agent jobs check to ignore disabled jobs.
3. Updated the database status check to account for mirrored databases.

The updated script can be downloaded from GitHub.
https://github.com/PatrickKeisler/SQLMorningHealthChecks

The link below is the original article with a complete rundown of how it works.
https://docs.microsoft.com/en-us/archive/blogs/samlester/sql-server-dba-morning-health-checks