Monitoring with PowerShell: Monitoring SQL server health

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.

Recent Articles

The return of CyberDrain CTF

CyberDrain CTF returns! (and so do I!)

It’s been since september that I actually picked up a digital pen equivalent and wrote anything down. This was due to me being busy with life but also my side projects like CIPP. I’m trying to get back into the game of scripting and blogging about these scripts. There’s still so much to automate and so little time, right? ;)

Monitoring with PowerShell: Monitoring Acronis Backups

Intro

This is a monitoring script requested via Reddit, One of the reddit r/msp users wondered how they can monitor Acronis a little bit easier. I jumped on this because it happened pretty much at the same time that I was asked to speak at the Acronis CyberSummit so it kinda made sense to script this so I have something to demonstrate at my session there.

Monitoring with PowerShell: Monitoring VSS Snapshots

Intro

Wow! It’s been a while since I’ve blogged. I’ve just been so swamped with CIPP that I’ve just let the blogging go entirely. It’s a shame because I think out of all my hobbies it’s one I enjoy the most. It’s always nice helping others achieve their scripting target. I even got a couple of LinkedIn questions asking if I was done with blogging but I’m not. Writing always gives me some more piece of mind so I’ll try to catch up again. I know I’ve said that before but this time I’ll follow through. I’m sitting down right now and scheduling the release of 5 blogs in one go. No more whining and no more waiting.