Microsoft SQL Server Error Log Scanning

  • Rudy Panigas

    SSChampion

    Points: 10683

    Comments posted to this topic are about the item Microsoft SQL Server Error Log Scanning

    Rudy

  • James A Skipwith

    SSCommitted

    Points: 1808

    Very helpful article. I will certainly make use of this.

    Cheers, James

    James
    MCM [@TheSQLPimp]

  • M&M

    SSC-Insane

    Points: 21679

    Very useful article. I was looking for something like this for quite some time 🙂

    M&M

  • Anipaul

    SSC-Insane

    Points: 24681

    Good one...

  • hakkie42

    SSC Enthusiast

    Points: 151

    Aren't you missing a

    USE [ErrorLogStorage]

    GO

    in the database creation script? When I ran it, it created the ErrLogData table in master...

    Apart from that, a smart solution: why struggle with text files while you have a database 🙂

  • abacrotto

    SSC Veteran

    Points: 217

    I'm sorry for asking something that simple. Why would you use a Cursor and a loop for updating the server when you can use an SQL UPDATE statement instead ?

    Let's see the chunk of code:

    -- Cycle through the ErrLogData table and insert the server's name

    DECLARE SrvName_Cursor CURSOR FOR

    SELECT [SQLServerName] FROM [ErrorLogStorage].[dbo].[ErrLogData] WHERE [SQLServerName] IS NULL

    OPEN SrvName_Cursor

    FETCH NEXT FROM SrvName_Cursor

    WHILE @@FETCH_STATUS = 0

    BEGIN

    UPDATE [ErrorLogStorage].[dbo].[ErrLogData] SET [SQLServerName] = @@servername

    FETCH NEXT FROM SrvName_Cursor

    END

    CLOSE SrvName_Cursor

    DEALLOCATE SrvName_Cursor

    I understand you are trying to update [ErrorLogStorage].[dbo].[ErrLogData].[SQLServerName] with @@servernave when that same column is null. Right ? Well, there is an UPDATE statement that can do the same thing without the cursor. It looks like this:

    UPDATE

    [ErrorLogStorage].[dbo].[ErrLogData]

    SET

    [SQLServerName] = @@servername

    WHERE

    [SQLServerName] IS NULL

    This code is much simple and faster than the cursor one.

    What do you think ?

    Regards from Argentina, South America.

    Ariel.

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    thx

    i've been meaning to add this to my daily data collection. i'll probably just change the process to use SSIS to import data for numerous servers into a central database

  • Rudy Panigas

    SSChampion

    Points: 10683

    hakkie42 (2/14/2011)


    Aren't you missing a

    USE [ErrorLogStorage]

    GO

    in the database creation script? When I ran it, it created the ErrLogData table in master...

    Apart from that, a smart solution: why struggle with text files while you have a database 🙂

    Yes, you are correct. I guess my "Copy and Paste" skills need more work.

    I have updated the code with this correction.

    Thanks,

    Rudy

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10683

    abacrotto (2/14/2011)


    I'm sorry for asking something that simple. Why would you use a Cursor and a loop for updating the server when you can use an SQL UPDATE statement instead ?

    This code is much simple and faster than the cursor one.

    What do you think ?

    Regards from Argentina, South America.

    Ariel.

    Thanks Ariel. You are correct, the update command would work better. I should update the code.

    Thanks for comments,

    Rudy

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10683

    alen teplitsky (2/14/2011)


    thx

    i've been meaning to add this to my daily data collection. i'll probably just change the process to use SSIS to import data for numerous servers into a central database

    Excellent idea! If you do write an SSIS package for this collection it would be nice to see it here.

    Thanks,

    Rudy

  • ben.rosato

    SSCommitted

    Points: 1869

    What's the reason behind running an insert statement with EXEC? That's a lot of unnecessary work. I hope you don't code all you procedures this way.

  • Willem G

    SSC Eights!

    Points: 809

    Not knowing enough about SSIS, I devised another solution in which a central (management) server retrieves SQL error logs from managed servers and stores them in a central repository.

    It will allow you to ignore informational messages or 'false positives' based on server name and/or the time they occur (a good thing if you know you always get message 'Msg 1234' between 2 and 4 AM, but want to know about it when it occurs at another time). This means you can focus on the out-of-the-ordinary messages.

    If you like, you can have SQL mail you any remaining lines for inspection.

    Outline:

    - The process is designed to run each day and collect 24 hours of error log lines.

    - Collection is controlled by the management server, which fires an SP to start the collection process.

    - The management SP then passes control to an SP on the managed server (= linked server).

    - The SP on the managed server retrieves all lines from the last error log and stores these in a local holding table. If ERRORLOG contains fewer than 24 hours (server reboot or log reinitialization), the SP will read older logs (ERRORLOG.1-6), until the collections spans 24 hours (or logs are exhausted..).

    - The management SP then continues and copies the lines collected on the managed server to a holding table on the management server. In the process, a servername is added and simple deduplication is done (duplicates are not copied, but increase the 'count' column for that particular line, while preserving info on first occurrence and spid).

    - Next, a cleanup is done on the holding table, by comparing all entries to an 'exclude table'. The exclude table contains the text of all entries you want to ignore and has options to specify a server name, a start time and an end time (ignore only if between start time and end time)

    If you make a good job of defining your exclude table, my experience is that very few lines remain, and those are the ones you want to know about!

    It is too much code post in a reply, so I will add the code as attachments.

    First, apply these scripts to a management database on your management server:

    cre_table_SQLErrorLog.txt

    cre_table_SQLErrorLog_exclude.txt

    cre_sp_Retrieve_Remote_Errorlog.txt

    cre_sp_Cleanup_Errorlog_Holding_Table.txt

    Check, adapt and execute the script of lines to be ignored:

    pop_table_SQLErrorLog_exclude.txt

    Next, apply this script to each managed server (will reside in msdb)

    cre_sp_Dump_Errorlog.txt

    If you have DTC running on each server (I do not..) you can avoid the SP + temp table on each managed server. You can then run everything from the management server and manipulate/insert the results from a remote exec of SYS.XP_READERRORLOG on the management server.

    Please feel free to mail any improvements.

  • Rudy Panigas

    SSChampion

    Points: 10683

    ben.rosato (2/14/2011)


    What's the reason behind running an insert statement with EXEC? That's a lot of unnecessary work. I hope you don't code all you procedures this way.

    This code was actually write a long time ago and is in need of an overhaul but it's working fine.

    It would be nice to see what updated code others can provide. I just may update the code completely with all the suggestions when I have some down time.

    Thanks

    Rudy

  • Rudy Panigas

    SSChampion

    Points: 10683

    Willem Gossink (2/14/2011)


    Not knowing enough about SSIS, I devised another solution in which a central (management) server retrieves SQL error logs from managed servers and stores them in a central repository. .....

    For an example of an exlude table, see

    pop_table_SQLErrorLog_exclude.txt

    I think you should write an article for SQL Central with your idea.

    Thanks.

    Rudy

  • Willem G

    SSC Eights!

    Points: 809

    I did, a while ago, on another topic (storing perfmon counters in a SQL central repository), but it was never picked up.

    At least in this way I do not have to wait for someone to follow up on it ..:-)

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

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