Seems like this is the week of SQL server blogs! This time we’re going to cover monitoring the SQL server health. SQL server health monitoring is important to keep all line of business applications in check and to make sure they perform well. We’ll be focussed on monitoring the server, databases, and jobs.
We will use the same trick as we did in the last SQL post. We’re going to be using the SQL Server module called SQLPS, which loads a PSDrive to browse all databases and get the state of each database. So let’s get started!
The script
I’d like to take a moment to point at that this script offers only very basic monitoring. This is often enough for MSPs and non-dba type administrators. If you want more extensive monitoring you should really look into the amazing dbatools module by Chrissy Lemaire and her team of amazing PowerShell admins/dbas. 🙂
The script alerts on databases that are not in a normal state, That have a recovery model other than “Simple” and where a database max size has been set. Also we’re checking if the database is located on C:\. You might want to comment out one of these if you do not care about one of these settings. These are just some of the things that we look out for in our environments. Its fairly straight forward to edit this script to monitor the backup dates instead, or if the database has the correct collation.
import-module SQLPS
$Instances = Get-ChildItem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)"
foreach ($Instance in $Instances) {
$databaseList = get-childitem "SQLSERVER:\SQL\$($ENV:COMPUTERNAME)\$($Instance.Displayname)\Databases"
$SkipDatabases = @("Master","Model","ReportServer","SLDModel.SLDData")
$Errors = foreach ($Database in $databaselist | Where-Object {$_.Name -notin $SkipDatabases}) {
if ($Database.status -ne "normal") {"$($Database.name) has the status: $($Database.status)" }
if ($Database.RecoveryModel -ne "Simple") { "$($Database.name) is in logging mode $($Database.RecoveryModel)" }
if ($database.filegroups.files.MaxSize -ne "-1") { "$($Database.name) has a Max Size set." }
if ($database.filegroups.files.filename -contains "C:") { "$($Database.name) is located on the C:\ drive." }
}
}
if (!$errors) { $HealthState = "Healthy" } else { $HealthState = $Errors }
And that’s it! Of course, modify these scripts to your own environment and requirements. And as always, Happy PowerShelling.
Hi – this looks like a great script that I would like to implement in our small MSP. I’ve saved it to a powershell file and I am trying to run it manually. It appears that the first line drops me into the “PS SQLSERVER:\>” area with the following message, but then does not proceed to do anything further:
“WARNING: The names of some imported commands from the module ‘SQLPS’ include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.”
What am I missing? Thank you for your time!
Normally you pass these scripts through to your own RMM system, but if running the script manually you can check the health state by executing the script, and then running $HealthState.
Thank you for the reply Kelvin!