February 13, 2011 at 3:39 pm
Comments posted to this topic are about the item Microsoft SQL Server Error Log Scanning
Rudy
February 14, 2011 at 1:42 am
Very helpful article. I will certainly make use of this.
Cheers, James
James
MCM [@TheSQLPimp]
February 14, 2011 at 1:54 am
Very useful article. I was looking for something like this for quite some time 🙂
M&M
February 14, 2011 at 2:39 am
Good one...
February 14, 2011 at 2:59 am
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 🙂
February 14, 2011 at 4:48 am
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.
February 14, 2011 at 6:33 am
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
February 14, 2011 at 7:42 am
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
February 14, 2011 at 7:44 am
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
February 14, 2011 at 7:46 am
alen teplitsky (2/14/2011)
thxi'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
February 14, 2011 at 8:00 am
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.
February 14, 2011 at 8:03 am
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.
February 14, 2011 at 8:14 am
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
February 14, 2011 at 8:16 am
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
February 14, 2011 at 8:23 am
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 34 total)
You must be logged in to reply to this topic. Login to reply