Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have a requirement for tracking some usage on a table that will be getting retired in the next year and feel that I could get the pertinent data (stored procedures and in-line sql being used against it) from the transaction logs. I've see some expensive purchased options out there for reading the logs but I was wondering if anybody knew of any opensource solutions or some sample code of how to parse these logs?

share|improve this question
What exactly you want to see in logs. Like the queries Select * from TableName or Insert into tableName values('') ? – abcdefghi Jun 19 '12 at 20:30
Yes and yes. Basically all reads and writes for a given table. Before we nuke the table we need to be able to address 99% of the systems using it. – RockyMountainHigh Jun 19 '12 at 21:28
2  
@RockyMountainHigh - Reads aren't recorded in the log and writes aren't at a level that you can see the individual queries. – Martin Smith Jun 19 '12 at 22:04

migrated from stackoverflow.com Jun 19 '12 at 22:57

2 Answers

up vote 8 down vote accepted

Logs are proprietary and those solutions are expensive because it is not simple and the average person does not know how to do it. Since you're using SQL Server 2008, have you considered some other more accessible options, such as:

I wrote about some of these here (before 2008 was released):

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

share|improve this answer
Thanks @Aaron. Thanks for your post. I have tried the first five. They have me most of the way there. (the last is simply not an option) We have 100's of systems built over years using this table and using different solutions for accessing it. I need the last mile solution. And it needs to be running behind the scenes so to speak. That said, it doesn't need to use the transaction log. Just needs to capture all reads/writes to a given table. Tracing, with 100+ million interactions a day, is a little tough to swallow. – RockyMountainHigh Jun 19 '12 at 21:26
A filtered server-side trace, running on the server, should not be capturing 100+ million interactions a day. For example, you should be able to filter out immediately on application names that you know connect to that table (and in fact also applications that you know do not connect to that table). – Aaron Bertrand Jun 19 '12 at 22:06
That is definitely something I will be presenting as an option. Our production dba's are very reluctant because with the amount of traffic we get the logs can grow quite large. I'm researching the SQL audit further as well. May have dismissed it too soon. Thanks! – RockyMountainHigh Jun 19 '12 at 22:27
There is nothing wrong, also, with consuming your trace files more regularly and culling the important data, storing it somewhere, and discarding the file as soon as you have that information. If you do this frequently enough, size shouldn't be an issue. And also regularly updating the trace to filter new apps that you've already identified (after you've identified an app, there is no reason to keep capturing queries from that app in the trace, right?). – Aaron Bertrand Jun 19 '12 at 22:29
1  
Well why do you think there aren't a bunch of scripts out there that read data from the log? The log doesn't store your actual DML query, it stores what it took for the engine to take what your query said to do, and to do it. – Aaron Bertrand Jun 20 '12 at 13:40
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.