Automate SQL Server Log Monitoring

  • Willem G

    SSC Eights!

    Points: 809

    Comments posted to this topic are about the item Automate SQL Server Log Monitoring

  • Jagadish Kumar Punnapu

    SSChasing Mays

    Points: 645

    Thank you Willem for the good Article.

  • Seed Vicious

    SSC Veteran

    Points: 297

    Exelent post!

    I use XP_READERRORLOG to do something similar, but i need to say: sometimes XP_READERRORLOG enter in an infinite loop than can't kill (only reset of course)

    😉

  • webrunner

    One Orange Chip

    Points: 29863

    Seed Vicious (9/16/2013)


    Exelent post!

    I use XP_READERRORLOG to do something similar, but i need to say: sometimes XP_READERRORLOG enter in an infinite loop than can't kill (only reset of course)

    😉

    Thanks - could you post any more information on this, such as a specific error or one of the Microsoft fixes or support documents about this issue? It seems like a big enough concern that others would have reported it.

    All I found in a quick Google search was the following, but I'm not sure it's what you're talking about.

    FIX: Problem Using Xp_readerrorlog When Reading Large Error Log

    http://support.microsoft.com/kb/157804/EN-US

    Thanks again for any help,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Seed Vicious

    SSC Veteran

    Points: 297

    webrunner (9/16/2013)


    Thanks - could you post any more information on this, such as a specific error or one of the Microsoft fixes or support documents about this issue? It seems like a big enough concern that others would have reported it.

    All I found in a quick Google search was the following, but I'm not sure it's what you're talking about.

    FIX: Problem Using Xp_readerrorlog When Reading Large Error Log

    http://support.microsoft.com/kb/157804/EN-US

    Thanks again for any help,

    webrunner

    The problem that I talk, is the High CPU that use when fail (for any reason) on SQL 2005/8/r2; Microsoft resolve it on this fix http://support.microsoft.com/kb/973524

    In my case, i have a lot of "political" problems to apply fixes. 😛

  • webrunner

    One Orange Chip

    Points: 29863

    Seed Vicious (9/16/2013)


    webrunner (9/16/2013)


    Thanks - could you post any more information on this, such as a specific error or one of the Microsoft fixes or support documents about this issue? It seems like a big enough concern that others would have reported it.

    All I found in a quick Google search was the following, but I'm not sure it's what you're talking about.

    FIX: Problem Using Xp_readerrorlog When Reading Large Error Log

    http://support.microsoft.com/kb/157804/EN-US

    Thanks again for any help,

    webrunner

    The problem that I talk, is the High CPU that use when fail (for any reason) on SQL 2005/8/r2; Microsoft resolve it on this fix http://support.microsoft.com/kb/973524

    In my case, i have a lot of "political" problems to apply fixes. 😛

    Great, thanks for the info.

    Yes, I know all about "political" problems in cases of SQL patching!

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • pchen 67085

    SSC Rookie

    Points: 31

    Good scripts.

    It was running into the permission error running the stored procedure which creates the table in tempdb on linked server. Why it creates a real table?

  • Willem G

    SSC Eights!

    Points: 809

    pchen 67085 (9/17/2013)


    Good scripts.

    It was running into the permission error running the stored procedure which creates the table in tempdb on linked server. Why it creates a real table?

    I create a 'true' table in tempdb on the linked server because the other options of creating a temp table (as a variable or prefixed by ##) do not persist after the session has ended. The INSERT is done through sp_executesql in session 1 and the retrieval of these records is done in another session.

  • OldFashionGang

    SSC Enthusiast

    Points: 121

    Nice article. But I can see 1 significant problem. To perform ErrorLog scan the SQL Server must be running and healthy on target host. But usually most interesting things appears in ErrorLog when it is not. I prefer to use another approach: scan ErrorLog as a text file. This could be done with various script languages. My first solution was Perl based. I saw some powershell scripts for this at msdn forums several times. Now because of security policy at my current job i cannot use any powerful script languages. So i applied finalized *.exe utils approved by our security guys (LogParser.exe, diff.exe and blat.exe for mail) plus plain *.bat scenario. I mean it`s enough ways but the main goal is to check ErrorLog contents whenever SQL Server itself runninng and healthy or not.

  • tan 17146

    Grasshopper

    Points: 15

    Excellent article. Works well with SQL2008R2, but does not appear working with SQL2012 and\or SQL2014. Wondering if you have any updates. Thanks.

  • Willem G

    SSC Eights!

    Points: 809

    tan 17146 (5/5/2015)


    Excellent article. Works well with SQL2008R2, but does not appear working with SQL2012 and\or SQL2014. Wondering if you have any updates. Thanks.

    Yes, I have an updated version that works with SQL 2012, 2008 (R2) and 2005. Possibly (probably?) also with 2014, but I was unable to verify. Feel free to try!

    Updated Nov 4: Updated script removed, the scripts in the (republished) article now support SQL 2012.

  • PAH-440118

    Ten Centuries

    Points: 1150

    OldFashionGang (9/23/2013)


    Nice article. But I can see 1 significant problem. To perform ErrorLog scan the SQL Server must be running and healthy on target host. But usually most interesting things appears in ErrorLog when it is not. I prefer to use another approach: scan ErrorLog as a text file. This could be done with various script languages. My first solution was Perl based. I saw some powershell scripts for this at msdn forums several times. Now because of security policy at my current job i cannot use any powerful script languages. So i applied finalized *.exe utils approved by our security guys (LogParser.exe, diff.exe and blat.exe for mail) plus plain *.bat scenario. I mean it`s enough ways but the main goal is to check ErrorLog contents whenever SQL Server itself runninng and healthy or not.

    I agree that I like to be able to access the logs from outside of the SQL. I have a SSRS report I created. This resides on a central management server. Using credentials supplied by the person running the report it reads the logs since midnight of the day before and then drops 'common' items, which I have recorded in a table on the management server. This is then all presented in a nicely formatted SQL report. This report can then be pointed at any SQL server I want to check the logs for. 😀

  • casilvis

    SSC Enthusiast

    Points: 145

    Nice article, just installed it in our shop.

    I found a problem with the script on a 2014 server, it wanted to have an 'N' in front of the quotes for the string parameters.

    Other than that though it worked like a champ, thanks for posting!

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the good writeup.

  • Willem G

    SSC Eights!

    Points: 809

    Update Nov 4:

    Although the text in this article does not mention it, the scripts in the re-published article now support SQL 2012.

    The (updated) scripts will also allow you to exclude records with variable information such as numbers or names. You can use them to exclude entries like: "AppDomain % created", where the name of the AppDomain varies. To do so, it uses PATINDEX instead of CHARINDEX.

Viewing 15 posts - 1 through 15 (of 17 total)

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