Post reply

Automate SQL Server Log Monitoring

  • September 15, 2013 at 10:19 pm

    #298777

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

  • September 16, 2013 at 1:16 am

    #1650322

    Thank you Willem for the good Article.

  • September 16, 2013 at 10:10 am

    #1650517

    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)

    😉

  • September 16, 2013 at 12:45 pm

    #1650578

    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 asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • September 16, 2013 at 1:39 pm

    #1650594

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

  • September 16, 2013 at 1:43 pm

    #1650596

    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 asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • September 17, 2013 at 9:38 am

    #1650838

    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?

  • September 20, 2013 at 1:29 am

    #1651789

    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.

  • September 23, 2013 at 5:46 am

    #1652299

    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.

  • May 5, 2015 at 2:31 pm

    #1795750

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

  • May 8, 2015 at 7:36 am

    #1796572

    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.

  • October 30, 2015 at 4:52 am

    #1836627

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

  • October 30, 2015 at 9:31 am

    #1836737

    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!

  • October 30, 2015 at 2:21 pm

    #1836827

    Thanks for the good writeup.

  • November 2, 2015 at 2:15 am

    #1837003

    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