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?
migrated from stackoverflow.com Jun 19 '12 at 22:57
This question came from our site for professional and enthusiast programmers.
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):
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)
2.The fn_dump_dblog function is used to read transaction log native or natively compressed backups. The result is similar:
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
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