locked
Need some sort of SQL Server error log parser? RRS feed

  • Question

  • I have over 100 MSSQL instances - ranging from SQL2000 - SQL2012.

    Currently, I'm querying using EXEC SP_ERRORLOG to read the log files. With the querying method, I cannot always be on top of things as I like. And frankly, I just check once a day for a period of 24 hours or so.

    I'm wondering if there's an utility / software / solutions out there that does the following:

    1) constantly monitors the error logs of all instances

    2) present the log files in an orderly manner for each instances as needed.

    3) email out a group of people for certain error / severity / state number. 

    Putting this in SQL Server Security because I'm mainly interested in errors like 18456 Login failed either from reboot or actual login failed.

    Thanks


    • Edited by Ami2013 Wednesday, January 21, 2015 2:43 PM
    Wednesday, January 21, 2015 2:39 PM

Answers

All replies

  • Hi,

    I have not used tool but simply know below. This could be of little help to you

    sp_readerrorlog 0,1,'string_you_want_to_search'

    You would find below link helpful

    http://www.mssqltips.com/sqlservertip/2307/automate-monitoring-sql-server-error-logs-with-email-alerts/


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, January 21, 2015 3:03 PM
  • You can try that using Powershell

    srvr.txt should contain a list of servers, I would prefer same SQL version.

    Save the content to ReadingErrorlog.PS1

    param
    (
    [String] $infile,
    [DateTime] $date,
    [int] $last,
    [String] $OutFile
    )
    
    function getUNCPath($infile)
    {
    $qualifier = Split-Path $infile -qualifier   
    $drive = $qualifier.substring(0,1)   
    $noqualifier = Split-Path $infile -noQualifier 
    "$drive`$$noqualifier"
    }
    
    $srvr=get-content c:\DBAScripts\srvr.txt
    
    foreach($svr in $srvr)
    {
    
    $UNC=getUNCPath($infile)
    
    $readbuffer=get-content -path \\$svr\$UNC -encoding unicode | select-object -last $last
    
    $i=0
    
    [Datetime] $date
    [String] $Outbuffer
    $outbuffer=""
    
    foreach($line in $readbuffer)
    {
                    $i++;
                    $mydate=$line.substring(0,22)
                    Write-host $Mydate
                    if ($line -ne "")
                                    {
                                    if($mydate -ge $date)
                                                    {
                                                    if ($line -like "*Error:*")
                                                                    {
                                                                    write-output "$i: $line"
                                                                    $outbuffer=$outbuffer+$line
                                                                    
                                                                    }
                                                    }
                                    }
    }
    add-content -path $outfile -value $outbuffer 
    }
    

    sample call

    PS:\>.\ReadingErrorlog.PS1  "f:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\errorlog"  "2012-05-01 12:00:00.00"  10  c:\DBAScripts\output.txt

    You may need to re-work on the code here and there.

    --Prashanth

    Wednesday, January 21, 2015 3:21 PM
  • There are a bunch of options that you can explore. It would be difficult to get a ready made solution but there are different ideas which you can use to develop into such a solution. All the places I worked had custom solutions which works along the same lines.

    Check these links

    http://www.mssqltips.com/sqlservertip/2307/automate-monitoring-sql-server-error-logs-with-email-alerts/

    http://sqlmag.com/database-performance-tuning/automate-sql-server-error-log-checking

    http://sqlknowledge.com/2011/01/monitoring-setting-alerts-for-sql-server-critical-errors/comment-page-1/

    Also you can use Powershell

    http://blogs.technet.com/b/heyscriptingguy/archive/2012/10/22/use-powershell-to-parse-sql-server-2012-error-logs.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    • Edited by Ashwin Menon Wednesday, January 21, 2015 3:29 PM
    • Marked as answer by Ami2013 Wednesday, January 21, 2015 10:12 PM
    Wednesday, January 21, 2015 3:28 PM
  • Thank you all for the help and suggestions. Good to know I'm not alone in trying to look for a ready made solution.

    I will definitely look through the suggestions and test them out.

    Wednesday, January 21, 2015 7:02 PM
  • You can set up alerts from the SQL Server Agent node in SSMS. Hm, well, with 100 servers, you want to do it with Powershell.

    You should have alerts for everything of severity >= 20, and for message 825, which is only an informational message. But it is a message that indicates that your disk I/O subsystem is about to crumble.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 21, 2015 10:23 PM