Check SQL Error Logs using Powershell

  • Comments posted to this topic are about the item Check SQL Error Logs using Powershell

  • Hi,

    I am receiving the following error when I run the script...

    7/26/2013 12:51:31 PM - Checking Agent Error Logs on BOSSQL03...

    Unable to read SQL Agent error log from server @{serverName=BOSSQL03}

  • Using SSMS, does this work:?

    EXEC master..xp_readerrorlog

  • Thanks for sharing. That's a relatively quick and dirty way to get the error log entries from multiple systems. I don't yet have PS3 installed, so I modified your code to use sqlps and the invoke-sqlcmd cmdlet and it works as desired. Hope you don't mind me sharing that. If so, I can remove the post.

    <#

    _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.

    S.Kusen 2014-08-26: Modified to use SQLPS and invoke-sqlcmd for powershell v2 environment.

    Requirements:

    Module invokesqlquery needs to be installed.

    1. Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com/

    #>

    cls

    import-module SQLPS

    $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-sqlcmd -Query @'

    select distinct serverName from YOUR_TABLE

    order by serverName

    '@ -serverinstance 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-sqlcmd -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-sqlcmd -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")

  • nice, thank you!

  • 09/01/2014 13:17:19 PM - Checking Agent Error Logs on SOME SERVERNAME...

    Unable to read SQL Agent error log from server @{serverName=SOME SERVERNAME}

    I'm experiencing the same problem with both methods: Dynamically or static

    Any suggestions

  • Without seeing your actual script the error could be caused by a permissions issue...

  • I'm sysadmin when running this and this is the script

    <#

    _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 MYCOLUMN from dbo.MYTABLE

    order by MYCOLUMN

    '@ -server MY SERVER -database MYDB

    # 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")

    This is the result:

    02-09-2014 08:14:52 - Checking SQL Error Logs on ...

    Unable to read SQL error log from server @{instancename=SERVERNAME1\INSTANCE1 }

    02-09-2014 08:15:07 - Checking SQL Error Logs on ...

    Unable to read SQL error log from server @{instancename=SERVERNAME1\INSTANCE2 }

    02-09-2014 08:15:07 - Checking SQL Error Logs on ...

    Unable to read SQL error log from server @{instancename=SERVERNAME2\INSTANCE3 }

    02-09-2014 08:15:07 - Checking Agent Error Logs on ...

    Unable to read SQL Agent error log from server @{instancename=SERVERNAME1\INSTANCE1 }

    02-09-2014 08:15:07 - Checking Agent Error Logs on ...

    Unable to read SQL Agent error log from server @{instancename=SERVERNAME1\INSTANCE2 }

    02-09-2014 08:15:07 - Checking Agent Error Logs on ...

    Unable to read SQL Agent error log from server @{instancename=SERVERNAME2\INSTANCE3 }

    I ran the script from ISE both as administrator and my account (which is sysadmin)

    What permissions do I need other than that?

    Thanks for your support

  • and EXEC master..xp_readerrorlog runs fine on all instances 2008r2, 2012sp1, 2014

    I can see from the trace and the output that I'm able to retrieve the serverlist from the table so that part should be in order

  • Thanks for the script.

  • Jim Breffni or anyone, anyway to add logic to include/exclude events per server. Example if we have a misbehaving server that has bogus errors how can we exclude the events just for this server?

    Love the script use it daily....

  • Learn_something_new_everyday (11/18/2015)


    Jim Breffni or anyone, anyway to add logic to include/exclude events per server. Example if we have a misbehaving server that has bogus errors how can we exclude the events just for this server?

    Love the script use it daily....

    Not a very clean way to do it, but you could filter on the line itself (without refactoring all of the code to accept exceptions) by changing the "out-gridview" lines to something like this:

    $allfiltered = $all | where-object {$_.ProcessInfo -notlike "*Backup*"}

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

    $allfiltered = $all | where-object {$_.ProcessInfo -notlike "*Backup*"}

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

    The goal of doing the filtering that way would be to filter the $all variable that contains all of the errors.

    Hope that helps get you in the right direction.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply