Introduction

The SQL Server errorlog has a misleading name. There is a lot more information in it than just errors. The version, startup parameters, memory settings and a ton more. I’ve been working with SQL Server for over 20 years and I still see messages in the errorlog that I don’t remember coming across. Since there are so many possibilities of what is being written to the errorlog, I can’t possibly search for all the things that I want to be notified of if they show up.

The answer I came up with is to do the opposite. If I look at today’s log and yesterday’s and last week’s, they all are mostly the same. With that in mind, I’m going to show you how to user PowerShell to search through the log and filter out all the normal stuff and return only the unusual.

Get-DbaErrorLog

First let’s get some errorlog records. Errorlog records from dbatools Get-DbaErrorLog look like this with each record being an object.

 
     
        
Get-DbaErrorLog -SqlInstance Server01 
ComputerName : Server01
InstanceName : MSSQLSERVER
SqlInstance  : Server01
LogDate      : 8/31/2021 12:00:00 AM
Source       : spid289
Text         : Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64)
                Mar 22 2021 18:10:24
                Copyright (C) 2019 Microsoft Corporation
                Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0  (Build 17763: )

I could look at just the Text property but I also want LogDate and SqlInstance so I know where and when the log message came from. Since I’m going to be passing this to Select-String, I going to use a Calculated Property to flatten the object.

    
        
# We'll pass the output of Get-DbaErrorLog to this.
Select-Object @{n='Log';e={"$($_.LogDate) $($_.SQLInstance): $($_.Text)"}}
Now we get an output looking something like this 
    
2021-08-31 00:00:00 Server01: Server process ID is 10904.

Select-String

To eliminate the lines I don’t care about, I’m going to use the -NotMatch switch for Select-String. This is the real hero of this story. Everything that doesn’t match a line in the filter file is returned. I create a text file with one pattern per line and use Get-Content to put it into a variable like so

    
        
$filter = Get-Content .\filter.txt

Here’s an example of what’s in my filter file. 

    
SQL Server detected 1 sockets
SQL Server is starting
UTC
Server process
System Manufac
Authentication mode
Logg
BACKUP DATABASE
The error log
Microsoft Corp
All rights
The service account
Database backed up

Lather, Rinse, Repeat

Now we’ve arrived at the meat of the process. 

    
        
# Get the output of Get-DbaErrorLog.
$log = Get-DbaErrorLog -SqlInstance Server01 -LogNumber 0
# Flatten the output
$flat = ($log | Select-Object @{n='Log';e={"$($_.LogDate) $($_.SQLInstance): $($_.Text)"}}).log
# Get my filter list
$filter = Get-Content .\filter.txt
# Pipe the flattened log to Select-String using -NotMatch 
# We only care about the "line" property that Select-String returns
$filtered = ($flat | Select-String -Pattern $filter -NotMatch).line
# Show my results.
$filtered

This is an iterative process. Run it look to see what the output is and add to your filter file. Keep running it until you only get the output you care about. Now change the -LogNumber to 1 and repeat the process. as your filter file grows you can remove the -LogNumber parameter altogether and search all the logs.

My threshold for the log output is things that I want to get paged for. Maybe have a “Email Me” filter and a “Page Me” filter.

Now in the process I pick a new server and go through it’s logs. After adding any additional filter lines I pick another server. Once I get to the point of not adding any lines I run it against all my servers.

All the Servers

Most of the dbatools commands that have a -SqlInstance parameter accept a list. I usually use my Central Management Server list using the Get-DbaRegServer

    
        
$cms = Get-DbaRegServer -SqlInstance CMSServer01 -Group AllManagedServers

I can now use $cms for the -SqlInstance parameter.

Timing is everything

If I’ve done everything right, I now have my filter set up to only give me results I care about, but I don’t want to search the whole log every time. To limit what is searched I use the -After parameter for Get-DbaErrorLog. If I run my search on a schedule every 15 minutes I want to limit my search to the last 30 minutes to have some overlap. 

    
        
$log = Get-DbaErrorLog -SqlInstance $cms -After (Get-Date).AddMinutes(-30)

Summary

So there are the building blocks you can use looking through your error logs. You can schedule it, you can run it on demand or some other thing I haven’t thought of.

Hopefully this is useful to you and gives you some ideas to add to monitoring your estate.

Thanks for reading.