Can the Transaction Log Tell Us What Happened?

In all my years of teaching SQL Server classes and answering questions on public forums, one of the most commonly asked questions has always been “How can I see the information in a database’s transaction log?” Most people asking this question seem to want this information to be able to recover data that was lost due to the infamous "user error." Some people seem to get upset, or even irate, when told that Microsoft doesn't provide a facility for viewing the contents of the log, as if it's their unalienable right to be able to see this information. In my opinion, the log contents are akin to the source code, and buying a license to use the product should in no way guarantee that you'll be able to see exactly how SQL Server does what it does. The log format and its contents are proprietary information; part of the reason for not making this information freely available is that it would expose too much information about the way SQL Server actually works.

If you, or your users, perform an action, you should know what you did. You can prevent unwarranted actions, such as running DELETE operations that remove too many rows, by using triggers or by allowing DELETEs only through carefully coded and tested stored procedures. If you let your users do things they shouldn’t, you’ll need to clean up your own mess. You can also set up custom auditing mechanisms to get the information you want get from the log by using SQL Trace. SQL Server 2008 provides an even richer array of auditing capabilities.

The transaction log is intended for SQL Server’s internal use, such as when performing a rollback operation or restoring data changes that you've captured in a log backup. In addition, the log lets SQL Server protect your database from being corrupted, which could happen if there were a system failure in the middle of a data modification operation. Think of what might happen if the data was updated, but SQL Server hadn't yet updated all the index pointers before the failure occurred. If there were no transaction log recording what changes were being made, SQL Server wouldn't realize upon system restart that the index structures were inconsistent and corrupt. The log lets SQL Server run recovery on system restarts and undo any incomplete operations.

Although Microsoft doesn’t provide you with the ability to read the transaction log, there are third-party products that provide this functionality. The vendors developing these tools based their work on an undocumented command, DBCC LOG, which was initially added to SQL Server to help support providers track down strange system behavior. Back when I worked for product support at Sybase, we used DBCC LOG regularly, and in fact the online Sybase documentation still includes this command.

The first commercial log reader tool, from Lumigent, was actually acquired from a small consulting company that was run by a Sybase consultant. The consultant had written a log reader tool based on his knowledge of DBCC LOG and used this tool in his consulting business. Lumigent acquired the tool and made it commercially available. Other vendors began offering log reader tools, and SQL Server Magazine published a comparative review of the Lumigent and Red Gate Software log reader products in the article "When SQL Server Tool Vendors Compete, DBAs Win."

Looking at the article now, it's not obvious which SQL Server version is being discussed. The structure of the log records and the exact details that are returned with the DBCC LOG command changed substantially between SQL Server 2000 and SQL Server 2005, so any vendor that wrote a tool and examined the results of DBCC LOG would have to completely rewrite their product for SQL Server 2005. Throughout the review, no SQL Server version is mentioned. Finally, at the end of the review, a third log reader tool from ApexSQL is mentioned with the comment that it supports SQL Server 2005, which leads us to assume that Lumigent and Red Gate Software's tools don't support SQL Server 2005. And none of the vendors have announced support for SQL Server 2008. 

So what can you do? You can search the Internet for references to using DBCC LOG and the function that wraps around it: fn_dblog. You can try using the output from one of those commands to get an idea of what has been done to your data. However, your best course of action is to plan ahead, either by preventing unwanted changes before they happen or by defining a trace to your own specifications that you can analyze when needed.