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.
Kelvin Tegelaar
If you want to contact me directly you can find me on twitter here, or via email: Kelvin {at} limenetworks.nl
Latest posts by Kelvin Tegelaar (see all)
- Monitoring with PowerShell: Monitoring users that are blocked for login - January 20, 2020
- Monitoring with PowerShell: Monitoring Office 365 deleted users & License usage - January 14, 2020
- Documenting with PowerShell: Handling IT-Glue API security and rate limiting. - January 11, 2020