Take the 2-minute tour ×
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.

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
3  
@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
    
read this... sqlskills.com/BLOGS/PAUL/post/… –  user9399 Jun 22 '12 at 7:48
add comment

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

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 9 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

There are several SQL Server functions and commands (e.g. fn_dblog, fn_dump_dblog, and DBCC PAGE) that potentially provide a way to view the transaction log file content. However, significant knowledge of T-SQL is required to use them, some are undocumented, and the results they provide are difficult to be converted to a human-readable format. Following are the examples of viewing LDF file content using SQL Server functions and commands:

1.Here is an example using fn_dblog to read an online transaction log, with a result of 129 columns (only 7 shown here)

enter image description here

2.The fn_dump_dblog function is used to read transaction log native or natively compressed backups. The result is similar:

enter image description here

Unfortunately, no official documentation is available for fn_dblog and fn_dump_dblog functions. To translate the columns, you need to be familiar with the internal structure and data format, flags and their total number in a row data

3.DBCC PAGE is used to read the content of database online files – MDF and LDF. The result is a hexadecimal output, which unless you have a hex editor, will be difficult to interpret

enter image description here

So, there are different ways to open a transaction log file, and most of them do just that – opens it. It’s tricky to get any human readable information and make a use of it though

share|improve this answer
add comment

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.