Free Tool to read SQL Server 2005 / 2008 Transaction Log files

Say you need to provide a solution to log and show all Create, read, update and delete (CRUD) transactions for auditing, this is more a mandatory for SOX & HIPPA regulations.

Using the standard SQL Server methods which is an undocumented statement DBCC LOGINFO will get information, but the problem is it cannot be interpreted in simple terms as per the requirement. Hence the rise of third party tools in this area is evident. In order to enable to read SQL Server transaction log files and provide an in-house tool to show the CRUD transactions on a set of databases in a customized manner is a demanding task for a DBA (now a days). The other aspect is to use Server side trace (Profiler) to track the statements on the database (which will impact the performance if the server is already stressed out for resources)

As per the note above using undocumented DBCC command you will get:
DBCC LOG([,{01234}])

• 0 - Basic Log Information (default)
• 1 - Lengthy Info
• 2 - Very Length Info
• 3 - Detailed
• 4 - Full
  

Example:
DBCC log (, 4)

And it displays the following transaction log information:

• Current LSN
• Operation (string starts with LOP_XXXXX)
• Context (string starts with LCX_XXXX)
• Transaction ID
• Tag Bits (Hex)
• Log Record Fixed Length (int)
• Log Record Length (int)
• Previous LSN
• Flag Bits (Hex)
• Description (string)
• Log Record (Hex)
  

As it is undocumented on the Microsoft articles its very hard to interpret what lies beneath those HEX values, therefore using third party tools such as APEX-SQL Log Reader and Lumigent's Log Explorer are more helpful to such users that are needed to cater the management needs. But these all tools comes for a heavy price costing dollars in four digits.

Now comes the climax of the entire article, a tool is available which can read and display contents of transaction log files in a very informative manner. The tool is called Internals Viewer for SQL Server. This tool has three main features / sub-tools: Allocation Map, Page Viewer, and Transaction Log Viewer. Also this tool integrates with SSMS and works for both SQL Server 2005 and SQL Server 2008. This tool might not be as capable as the ones listed above, but the functionality it provides that too for free is unmatched with any other tools in my knowledge.

This tool can be a very handy and economic debugger in case of a SSIS Load package failure, especially in a parent-child load package architecture. In such an architecture, tens / hunderds of SSIS child packages are executed SSIS by parent / master packages, and the transaction handling becomes so complex, that architects carelessly / helplessly amends point-in-time database recovery as the failure recovery option or end up buying one of the tools mentioned above to rollback specific transactions to recover the damaged relational datawarehouse. This also give a good hint / example of how important transaction handling remains in an ETL / SSIS project.

As per the documentation of this tool, it offers the below mentioned features:

Integration with SSMS (SQL Server Management Studio) 2005 and 2008:

• The application is installed as a SSMS add-in
• Internals information integrated into the Object Explorer
• Transaction Log viewer integrated into the Query Results

Allocation Map:

• Displays the physical layout of tables and indexes
• Displays PFS status
• Overlay pages in the Buffer Pool

Page Viewer:

• Displays Data pages including forwarding records and sparse columns
• Displays Index pages
• Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
• Displays pages with SQL Server 2008 row and page compression

Below are some of the screen-prints of this tool:

References: sqlserver-qa.net