Searching the SQL Error Log with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Yesterday we looked at Reading Todays SQL Error Log Today we are going to search all* of the SQL Error Logs. This is usually used by DBAs to troubleshoot issues

The SQL Server Error Logs (by default) are located in the folder Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and are named as ERRORLOG.n files. The most recent has no extension the rest 1 to 6.

Using PowerShell you can easily find the location of the SQL Error Log using the ErrorLogPath Property

image

You can also read it with PowerShell using the ReadErrorLog Method. This has the following properties LogDate, Processinfo and Text. You can easily filter by any of those with a bit of PowerShell 🙂

I have created a function which takes two parameters $SearchTerm and $SQLServer adds *’s to the Search Term to allow wildcards and searches each of the SQL Error Logs

image

Simply call it like this and use the results as needed

image

Of course, as the results are an object you can then carry on and do other things with them

image

The code can be found here Search-SQLErrorLog

* Technically we are only searching the default number of 7 but if your environment is different you can easily add the lines to the function

Advertisements

4 thoughts on “Searching the SQL Error Log with PowerShell

  1. How could you exclude not looking for specific errors?
    I am trying to exclude certain error codes and text containing without errors. The only way I can seem to achieve exclusion of certain errors is listed in my code below. I would like to put the exclusion codes into variable instead of hardcoding into code, however I am not certain how to achieve this?

    ex.
    $Logs.ReadErrorLog() | Where{$_.LogDate -is [datetime] } |
    Where-Object{$_.LogDate -gt $DatetoCheck } |
    Where-Object{($_.Text -like “*Error*” -or $_.Text -like “*error*” -or $_.Text -like “*Fail*”) -and ($_.Text -notlike “*without errors*”) }|
    Where-Object{($_.Text -notmatch “1608|17832|17824”)}|
    % {$NewRow = $Results.NewRow();
    $NewRow.LogDate = $_.LogDate;
    $NewRow.ProcessInfo = $_.ProcessInfo;
    $NewRow.Text = $_.Text;
    $NewRow.SQLInst = $sqlServerName;
    $Results.Rows.Add($NewRow); }
    } catch {
    Write-Host “Error Reading $sqlServer.Name”
    }

    • Hi Aaron,

      I can make this work like this

      $NotShow =”18456|18452″
      $logDate = (get-date).AddDays(-1)
      $Results = $srv.ReadErrorLog(0) |Where-Object {$_.LogDate -gt $logDate}|
      Where-Object{($_.Text -like “*Error*” -or $_.Text -like “*error*” -or $_.Text -like “*Fail*”) -and ($_.Text -notlike “*without errors*”) -and ($_.Text -notmatch $NotShow)}|
      format-table -Wrap -AutoSize
      $Results

      or indeed taking the same thing further

      $NotShow =”18456|18452|without errors”
      $SHow = “Error|error|Fail”
      $logDate = (get-date).AddDays(-1)
      $Results = $srv.ReadErrorLog(0) |Where-Object {$_.LogDate -gt $logDate}|
      Where-Object{($_.Text -match $Show) -and ($_.Text -notmatch $NotShow)}|
      format-table -Wrap -AutoSize
      $Results

      Does that help?

      Rob

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s