Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Microsoft SQL Server Error Log Scanning Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 8:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:42 AM
Points: 361, Visits: 1,543
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.




Post #1063542
Posted Monday, February 14, 2011 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 35, Visits: 351
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.


  Post Attachments 
cre_table_SQLErrorLog.txt (12 views, 220 bytes)
cre_table_SQLErrorLog_exclude.txt (14 views, 187 bytes)
cre_sp_Dump_Errorlog.txt (14 views, 1.36 KB)
cre_sp_Retrieve_Remote_Errorlog.txt (17 views, 1.04 KB)
pop_table_SQLErrorLog_exclude.txt (12 views, 2.06 KB)
cre_sp_Cleanup_Errorlog_Holding_Table.txt (7 views, 3.18 KB)
Post #1063543
Posted Monday, February 14, 2011 8:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:19 PM
Points: 318, Visits: 1,127
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



Post #1063554
Posted Monday, February 14, 2011 8:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:19 PM
Points: 318, Visits: 1,127
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.



Post #1063557
Posted Monday, February 14, 2011 8:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:06 AM
Points: 35, Visits: 351
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 ..
Post #1063563
Posted Monday, February 14, 2011 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:33 PM
Points: 28, Visits: 212
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.
Post #1063718
Posted Monday, February 14, 2011 12:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 6:17 PM
Points: 16, 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

Post #1063804
Posted Monday, February 14, 2011 12:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:19 PM
Points: 318, Visits: 1,127
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.



Post #1063805
Posted Monday, February 14, 2011 4:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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!
Post #1063921
Posted Monday, February 14, 2011 5:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:44 PM
Points: 11, Visits: 532
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.
Post #1063937
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse