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:
- What is the uptime of each SQL Server?
- What is the status of each database?
- What is the status of each Availability Group?
- What is the backup status of each database?
- What is the available disk space?
- Are there any SQL Agent failed jobs in the last 24 hours?
- What errors appeared in the SQL errorlog in the last 24 hours?
For the updated script, I have added checks to answer these questions:
- What is the status of each SQL Server service?
- 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.
- Changed the error trapping to continue processing even if a SQL Server is unresponsive.
- Updated the SQL Agent jobs check to ignore disabled jobs.
- Updated the database status check to account for mirrored databases.
The updated script can be downloaded from GitHub.
The link below is the original article with a complete rundown of how it works.