Add colours to the ERRORLOG

When dealing with large logs I often find it difficult to find the information we are looking for. To make it easier, we can use PowerShell to add colours to the ERRORLOG based on string patterns

Note this will only work when using the Select-String command.

Remote ISE

Even though we need physical access to the ERRORLOG files, you do not need Remote Desktop. We can leverage PowerShell’s ISE ability to run Remote Tabs.

Run PowerShell ISE and click “New Remote PowerShell Tab” or Ctrl + Shift + R and provide credentials to the remote system. Once connected the remote server name will be visible before the prompt:

[sql-1]: PS C:UsersmarcinDocuments>

Get the Log

Next, we can get the path of the ERRORLOG using SMO:

Import-Module SQLPS
$SqlServer = new-object Microsoft.SqlServer.Management.Smo.Server  "localhost"
$ErrorLog = $SqlServer.ErrorLogPath
$LogPath = "$ErrorLogERRORLOG"

Add colours

Once we have the $LogPath we can get the content of the ERRORLOG with Select-String and a custom function to apply colours:

Import-Module SQLPS
$SqlServer = new-object Microsoft.SqlServer.Management.Smo.Server  "localhost"
$ErrorLog = $SqlServer.ErrorLogPath
$LogPath = "$ErrorLogERRORLOG"

Function Set-Color ([string]$Line) {
    $Line = $Line -replace "`n`r"
        if (1 -eq 2) {
            Write-Host "SKIP"
        }

        #OK
        elseif ($line -match 'successfully processed|Hardened Lsn|Starting up database|BACKUP DATABASE successfully processed|has successfully|found 0 errors') {
            Write-host $line -ForegroundColor Green}

        #INFO
        elseif ($line -match 'This is an informational message|Sending page request to|accepting vlf header|No changes detected from manager notification') {
            Write-Host $line -ForegroundColor Gray}

        #ERROR
        elseif ($Line -match 'error|fail|Unable to access|m_errcode') {
            Write-Host $Line -ForegroundColor Red}
        
        #WARNING
        elseif ($line -match 'has been suspended for the following reason|local replica received build replica request from|a missing log block|I/O requests taking longer than 15 seconds') {
            Write-Host $line -ForegroundColor Yellow}

        #anything else
        else {
            Write-Host $line
            }
}
select-string -Pattern "Log was backed up" `
    -Path $LogPath -NotMatch | ?{$_.Line -ne "" -and $_.Line -like "*"} | %{Set-Color($_.line)} | Format-list *

Result

Customisation

It is very easy to add custom rules or exclusions, just add strings you would like to apply colours into the relevant categories within the Set-Color function. To add exclusion add a custom string to the -Pattern switch in Select-String

Conclusion

Being able to quickly retrieve information from the ERRORLOG is critical in production environments, especially when dealing with an incident or debugging a problem. Adding custom colours together with PowerShell’s ability to handle pattern inclusion or exclusion we can greatly improve our experience with the ERRORLOG and find the information we are looking for much quicker.