How to Filter and Review SQL Error Logs on Multiple SQL Servers with PowerShell

When working with many related SQL Servers it can be helpful to review SQL Error Logs on multiple servers all at once while also filtering for certain types of error messages. There are many use cases for this script, but the most common use case I’ve encountered is to check for failed logins and connection timeouts after an Availability Group or Mirrored Database failover. I work in an environment with multiple Always On Availability Groups with dozens of webservers that connect to them via Listeners. When we do regular maintenance such as Windows Patching, we manually failover our Availability Groups as part of the patching process. With one run of this PowerShell script, we can ensure there are no failed logins or connection issues once the failovers and maintenance have been completed. Some of our Availability Groups span up to five Replicas, so there have been occasions where our Logins are out of sync.

To execute the script, two input files are necessary. First, create a “Servers.txt” file containing all the servers with error logs to review. The Servers.txt file should be saved in the same directory as the PowerShell script and should look like this (Just replace “Server#” with your real server names.):

Secondly create a “Error_Filter.txt” file containing the specific error messages you’re interested in searching for. This file should also be saved in the same directory as the PowerShell script and should look similar to this:

In the example above, we’re looking for any error message containing “login failed” or “connection timeout”. Any number of different filter criteria can be included in the search by simply adding a new line to this file.

Finally, save the PowerShell Script below as a .ps1 file. (In the example shown below, I’ve named the file CheckErrorLogs.ps1)

if (!(Get-PSSnapin -Name SQLServerCmdletSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SQLServerCmdletSnapin100}
if (!(Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SqlServerProviderSnapin100}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended") | Out-Null

$DateLimit = read-host "Please enter a minimum date/time in the following format:
[YYYY-MM-DD HH:MM]   i.e. 2015-01-01 15:30
Leave blank to omit the date time filter.

Enter Date"

$ServerList = Get-Content Servers.txt
$ErrorList  = Get-Content Error_Filter.txt
$Results=@()

foreach ($Server in $ServerList)
{
foreach ($Error in $ErrorList)
{
$Query = "SET NOCOUNT ON
CREATE TABLE #ERRORLOG (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX))
INSERT INTO #ERRORLOG
exec sp_readerrorlog
SELECT @@SERVERNAME AS 'ServerName', LogDate, [Text] FROM #ERRORLOG
WHERE [Text] LIKE '$Error'
AND LogDate > '$DateLimit'
DROP TABLE #ERRORLOG"

$Results += Invoke-Sqlcmd -Server $Server -Database "master" -Query $Query
}
}

$Results | Out-GridView

Once you’ve created the CheckErrorLogs.ps1 and the two text files, open a PowerShell window and execute the script. In the example below, I’ve saved all three files to the folder “C:\PowerShell\ReadSQLErrorLogs”.