SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Microsoft SQL Server Error Log Scanning


Microsoft SQL Server Error Log Scanning

Author
Message
ben.rosato
ben.rosato
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 1566
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
Willem G
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 535
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.
Attachments
cre_table_SQLErrorLog.txt (22 views, 220 bytes)
cre_table_SQLErrorLog_exclude.txt (21 views, 187 bytes)
cre_sp_Dump_Errorlog.txt (22 views, 1.00 KB)
pop_table_SQLErrorLog_exclude.txt (21 views, 2.00 KB)
Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 1312
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 Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 1312
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.



Willem G
Willem G
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 535
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 ..:-)
Slyfin
Slyfin
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 261
Another thing you could try is a powershell script to pull the logs from a collection of SQL servers and email the filtered results to yourself.
michael.wanke
michael.wanke
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 295
I do like the idea of removing the cursor. Either an update as already suggested or the SQLServerName column could be changed to use a default:


[SQLServerName] [nvarchar](150) NULL DEFAULT @@servername


Rudy Panigas
Rudy Panigas
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1366 Visits: 1312
michael.wanke (2/14/2011)
I do like the idea of removing the cursor. Either an update as already suggested or the SQLServerName column could be changed to use a default:


[SQLServerName] [nvarchar](150) NULL DEFAULT @@servername



Interesting but not sure if this change will work on a server with several instances installed.



UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3050 Visits: 2204
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 ?

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




Am I totally missing something here? Essentially doesn't that CURSOR/WHILE loop only result in the UPDATE running once, if there are any NULLs in the SQLServerName column, and setting the SQLServerName column to @@servername for ALL records regardless of if they already have a value? At which point the cursor shouldn't find any more records and abort. So to get the extact same behavior just change that block to: (Since we know the field will be NULL to start with.)

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



Also, I'm not sure why you are bothering to check if the table exists before the TRUNCATE TABLE statement:

-- Remove older data 
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
BEGIN
TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData]
END



since if it doesn't the INSERT will fail since there is no ELSE clause to CREATE the table. (It just moves the error from one line to another.)

Other than that, I think the article is nice and shows a decent way to get at the error log. Thanks!
sqldba.today
sqldba.today
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 629
I run something very similar as an SSIS package and pull all the log files from all my servers into a master table. I use xp_readerrorlog with the following parameters

EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, @startDate, @endDate, N'asc'

And I run it every night, but with a start date as current date - 2 and an end date as current date + 1. The I merge the results from each server into the master log table.

On top of that I run it again with EXEC master.dbo.xp_readerrorlog 0, 2, NULL, NULL, @startDate, @endDate, N'asc' to get the SQL Agent logs as well. Those go into another table just for agent logs.

I like doing this because I run a lot of SQL Servers on SAN, and once a while back we ran into some issues with the SAN Fabric and I used a query from all the server log files that were SAN attached to determine a pattern of the "I/O taking longer than 15 seconds" across the servers.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search