Check SQL Error Logs using Powershell

,

You check your SQL and Agent Error Logs every day, right?

Here is a powershell script to make that process easy.

Ideally you will have a list of servers stored somewhere in a database so that as servers are added/removed your script continues to work as expected.

  1. Make sure you have Powershell v3 installed, I haven't tested this script on v2 
  2. You will need to install module invokesqlquery, get it from http://powershell4sql.codeplex.com/
  3. Modify the query at line 26 to dynamically supply the names of your servers (recommended)
  4. Modify line 31 to supply the list of your servers manually (useful for testing)
  5. Run the script in the Powershell ISE environment or equivalent.
  6. All SQL errors from all servers are displayed in their own window that you can filter and search
  7. All SQL AGENT errors from all servers are displayed in their own window that you can filter and search
  8. Modify the date filter at line 21 to go back further than 1 day
  9. Modify the text filters at lines 40 and 41 to include/exclude other text
  10. Modify the first parameter of the call to EXEC master..xp_readerrorlog to look at older logs than the current - 0 = current log, 1,2,3 etc are any older logs
  11. That's it!  Have fun and start enjoying checking your error logs instead of it being a chore....
<#

_checkErrorLogs.ps1
Description:
Author: Jim Breffni.
This script displays the errorlog entries for all selected servers for the last n days.
The script can take a few minutes to run if the error logs are large and you are looking back over several days.

Requirements:
Module invokesqlquery needs to be installed.
1.  Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com/

#>

cls

import-module invokesqlquery
 
$today    = (get-date).toString()
$all      = @()
$lookback = ((get-date).adddays(-1)).ToString()  #  look back n days from current time

#  Load up the list of servers to check
#  Use this code if you can get your list of servers from a sql database
$servers = invoke-sqlquery -query @' 
select distinct serverName from YOUR_TABLE 
  order by serverName 
'@  -server YOUR_SERVER  -database YOUR_DATABASE

#  remove the comment from the code below if you want to supply the list of servers as an array
#$servers = 'sql01', 'sql02' | select-object @{Name = 'serverName'; Expression = {$_}}


foreach ($server in $servers) {

"$((get-date).toString()) - Checking SQL Error Logs on $($server.servername)..."

try {
$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 1, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
  where-object {$_.Text -match 'error|fail|warn|kill|dead|cannot|could|stop|terminate|bypass|roll|truncate|upgrade|victim|recover'} | `
  where-object {$_.Text -notmatch 'setting database option recovery to'}
}
catch {"Unable to read SQL error log from server $server"}
}  #  foreach ($server in $servers) {

$all | out-gridview -title ("$($all.Count) errors in SQL Server Error Logs. From $lookback to $today")


$all = @()
foreach ($server in $servers) {
"$((get-date).toString()) - Checking Agent Error Logs on $($server.servername)..."
try {
$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 2, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
  where-object {[datetime]$_.LogDate -ge  $lookback} 
}
catch {"Unable to read SQL Agent error log from server $server"}
}  #  foreach ($server in $servers) {

$all | out-gridview -title ("$($all.Count) errors in SQL Agent Error Logs. From $lookback to $today")

Rate

3 (2)

Share

Share

Rate

3 (2)