Log Explorer

Read your database's transaction log and root out errors

Lumigent Technologies' Log Explorer is the first—and so far the only—utility that reads SQL Server 7.0 transaction logs. Microsoft has never provided a tool for reading a SQL Server transaction log. You can use a query to display the operation codes in the SQL Server 6.5 syslogs table, and third-party products are available to read a SQL Server 6.x transaction log. But because SQL Server 7.0's (and SQL Server 2000's) transaction logs reside in their own files separate from data files, those SQL Server releases don't have a syslogs table. As a result, SQL Server 7.0 transaction logs have been inaccessible until the advent of Log Explorer.

Why would you want to read a database's transaction log? Suppose a user mistakenly deletes data. If you know approximately when the delete occurred, you might be able to determine which transaction log backup file or set of files contains the operation. And if you could read that transaction log, you could find the command that did the damage, the time the error occurred, and perhaps even who executed the command. With that knowledge, you might be able to rebuild the data or generate a SQL script to undo the changes.

For this review, I tested Log Explorer 1.0. Lumigent, though, is moving quickly, and as this issue went to press, the company had already released Log Explorer 1.3. These early versions of the product support only SQL Server 7.0. However, Log Explorer 1.5, which also supports reading SQL Server 2000 transaction logs, is in beta testing, and Lumigent will likely release it in first quarter 2001. According to the company, current Log Explorer owners will be able to upgrade for free to Log Explorer 1.5.

Attaching to a Log File

When you start Log Explorer, you must first attach either to a live log or to a log backup file. The current releases let you attach only to one database's live transaction log or to one transaction log backup file. If a live transaction log has several files, Log Explorer treats the log as one virtual log file. However, you can't currently view two or more sequentially produced transaction log backup files, so you can't view a transaction spanning several log backups.

To attach to a live transaction log, you must log in to SQL Server by using either a standard SQL Server login or a trusted connection. Only Log Explorer 1.3 or later supports trusted logins. I talk more about Log Explorer's security considerations later.

After you've attached to a log file, Log Explorer offers seven functions. The first function—switch to another log file—is self-explanatory, but let's cover the other functions in turn.

Retrieving a log summary. Log Explorer's Log Summary dialog box reports data about a specific transaction log. For example, the Log Summary lists the filename of the log you're attached to, the selected log file's name, the log's duration, the start and stop times for the last transaction log and full database backups, and log space usage data. Most Log Summary report columns are self-explanatory. Note, however, that if a database's transaction log consists of more than one transaction log file, you must choose which log file Log Explorer should attach to, and you can attach to only one log file at a time. Of course, if you back up the log to a single file, you can browse through the whole log.

Browsing the log. Browsing the transaction log lets you look at the details of a transaction log history. To help you navigate the sheer volume of a log's transactions, Log Explorer's Browse Filter dialog box lets you read from the beginning or end of the log or from a particular date. You can also limit the kinds of activities you want to see, the tables involved, the server process IDs (SPIDs) that identify SQL Server connections, the search depth (the amount of time spent on searching), and the database User ID (UID). If you're exploring a live database transaction log, you'll like Log Explorer's Refresh View button on the toolbar, which brings new transactions into the browse window.

When you click View Log in the lower left corner of the Browse Log dialog box, you get a log view that color-codes each transaction, which Figure 1, page 72, shows. The lower part of the Browse Log dialog box shows the data components of the SQL command involved in the selected transaction. The dialog box also shows old and new row data for UPDATE statements. You can even browse the log of a database that truncates its log on checkpoint, but you have to clear the Skip backed up log option in the Browse Filter dialog box. Log Explorer 1.3 labels this option Skip or View Recycled Data.

Undoing or rolling back modifications. Log Explorer provides two ways of reverting data to a prior state: Undo and Rollback. The Undo function generates a script to undo the effects of a given transaction. The Rollback function generates a script to roll back all changes to a given object up to a certain time point.

You can access the Undo function from the Browse Log dialog box and from other parts of the tool. As you're browsing through the log, you can select a given transaction and right-click to see a shortcut menu. From this menu, you can add or clear a bookmark, see the affected row's revision history, view the row's current data values, or undo the modification. To let you undo the transaction, Log Explorer generates an Undo script and saves it to a file along with the T-SQL necessary to undo the selected transaction. You can then apply the file to the database by using Query Analyzer or the osql utility. In Log Explorer's generated scripts, a T-SQL DELETE reverts a transaction's INSERT statement, an UPDATE reverts a transaction's UPDATE statement, and one or more INSERTs reverts a transaction's DELETE statement.

The Rollback function operates similarly to the Undo function. In the Rollback dialog box, you can instruct Log Explorer to generate a script to roll back changes to all tables or to a particular table and up to a certain time. Log Explorer generates the script and saves it to a file, which you can apply later. As you might expect, the rollback file can grow large, depending on how many changes you need to revert. When using the Rollback dialog box, make sure that you don't mistakenly specify a rollback time that's earlier than the log's end time, or Log Explorer won't generate the script.

Undeleting data. Not only does Log Explorer let you undo and roll back changes, but it can also generate a script to revert only the DELETE statements in a log. The Undelete script contains INSERTS against the selected object. The time requirement for Undelete is similar to the requirement for Rollback—you must specify a time that the log covers.

Viewing the row revision history. Log Explorer lets you see specific changes a user made against a particular row, as Figure 2 shows. You just specify a table's row in the RowId tab of the Row Revision History dialog box and optionally provide a log sequence number (LSN) to distinguish among duplicate rows and an appropriate time within the transaction log. From the Row Revision History window, you can inspect each change and selectively generate an Undo script for any change.

Viewing the row transaction history. You can also get a row's transaction history—complete with operation codes and LSN. You just specify a table's row in the RowId tab of the Row Transaction History dialog box by entering a value or set of values for the applicable columns, then choose a sequence number to identify the transaction.

Some Caveats

Log Explorer has a couple of minor problems and one major security concern. First, the minor problems. You can't inspect transactions that span a sequence of transaction log backups by attaching to a sequence of transaction log backup files. But Lumigent promises that feature in a future 2001 release. Also, using a utility to browse through a production database's live transaction log is risky; the safer choice is to browse through the backup file for that transaction log.

A major consideration for Log Explorer users is the security the application provides for reading the transaction log. A database transaction log isn't a security object in SQL Server 2000 or 7.0 because SQL Server doesn't include a tool to read the log. However, a transaction log does contain database data. The lack of security is a concern when you read both a live transaction log and a transaction log backup file.

To attach to a live transaction log, Log Explorer uses an extended stored procedure, xp_logattach, which installs in the master database. By default, no users hold execute permission to xp_logattach, so the only login that can execute that stored procedure to read the transaction log is the systems administrator (sa) login or logins with sa rights. However, by granting execute permission on xp_logattach to the public role in the master database, I was able to attach to and read a live database transaction log file using a login that didn't even carry Database Owner (DBO) rights to the database. The lesson: Restrict the rights to execute xp_logattach. And now that Log Explorer provides a tool to read transaction log backup files, it's just as important to control who can access the folders where these files reside as it is to control access to database backup files.

Log Explorer is a helpful tool for reading and analyzing SQL Server transaction logs (both live files and backup files), solving data problems, and reducing downtime. The tool is especially valuable for repairing a database that users or applications have damaged. Lumigent is offering Log Explorer at a per-server price of $1495. You can download a free trial version at http://www.lumigent.com.