Product Review: Log Explorer 2.0

,

How often has this happened to you? You just gave access to

a user to write to a database when he or she forgets a WHERE clause when deleting

data and accidentally deletes every row in the table. My favorite of these

types of examples was a HR application that would make a nice mistake for some

users when it would update everyone’s salary to be equal to the user you just

modified. I’ve fallen guilty to prematurely deleting a table after asking 12

users if it was OK. Of course it turned out that the 13th user needed

the table. If you’ve fallen victim to any of these scenarios, then Log Explorer

is a product that is a must-have in your tool library.

Log Explorer 2.0 is a product that allows administrators,

developers and analyst to peer into the transaction log and look at

transactions that have been committed. The nicest feature of the product is

that it does this with minimal server overhead. Log Explorer is able to achieve

this by installing a few extended stored procedures on your target SQL Server

and looking into the transaction log files, which are sequential. Some products

that promise the same type of auditing look into the actually relational system

of the database and create duplicate records of every server activity (from a

trigger). Since log explorer doesn’t look at the relational data, but the

physical log files, it is quite easy on your SQL Server. The downside of this

is that the files are not optimized for this type of usage. Since this type of

access is not optimal, Log Explorer can take extended periods of time to comb

through a large transaction log.

This type

of technology was first seen in Log Explorer by Platinum Technologies (now

Computer Associates). Log Explorer takes this much farther than any type of log

reading software I’ve seen. Here is some of its basic functionality:

 

  • Reading a transaction log. It

    can even do this in real-time mode, which shows you transactions as

    they’re being committed to your database.

  • Roll back individual

    transactions.

  • Recover a table that was

    dropped or truncated.

  • Export the contents of a

    transaction log to XML or other formats.

  • Perform in-depth database

    analysis based on the transaction log.

As you can imagine, reading the transaction log is Log

Explorer’s most basic feature (shown in the below screenshot). This functionality is available in all editions

of the product. There are two modes you can employ when viewing the transaction

log: real-time and static. If you look at the static transaction log, you can

filter the data to see transactions within a certain date. You can also filter

based on a given table, SPID, database role, or certain types of activity. A

handy feature here is that you can attach to an online transaction log file or

a backup.

One of my favorite features in Log Explorer is the ability

to roll back an individual transaction. Rather than roll back the transaction

real-time, it creates a SQL script, which can be run from within the tool by

clicking on Run SQL Script. Some may find this inability to rollback a

transaction directly inconvenient. I found it to be a nice safeguard, which

kept me from accidentally rolling back a transaction. To rollback a

transaction, you can simply click your right mouse button on a transaction and

select Undo Transaction. Large amounts of transactions can also be rolled back

by selecting Undo User Transaction.

Nothing is worse than having to kick out all the users of a

database to restore a database. If a user drops a table inadvertently or has

truncated the table, Log Explorer has the functionality to rollback that

operation. It will not recreate the schema but it will create the SQL script

needed to reload the table. I found that any type of rollback operation was

slow, but I was able to flawlessly recover from my mishaps using the product.

Undoing a table drop or truncation reclaims data pages on the free list and is

not a 100% reliable and very slow. The problem lies in the fact that the data

page you’re trying to restore may be reclaimed. This has been fixed in Log

Explorer 2.1, which was in beta at the time of this articles publication.  Log Explorer 2.1 has the ability to read

this information from a backup file and then recover the selective tables from

it.

Another feature you can use in Log Explorer is the ability

to export the data from the transaction log to another non-proprietary format.

This is beneficial if you have the need to analyze the data for how many

inserts, deletes, and updates are occurring. Also, it captures the table that

these actions occurred. This leads to the next feature that is built into Log

Explorer and that is the ability to automatically analyze your log for this

type of information by clicking the Log Analysis button (shown below). This is a great

feature for admins who need to find out how taxed a database is. You can plan

indexes based on how many inserts are occurring in tables. The analysis will

shows you how many of each action occurred and to which tables.

I only had two real issues with Log Explorer. The interface

is a little clunky and hard to navigate around. For example, in some areas the

screen does not scale. If the screen is not maximized in some areas, you won’t

be able to click on the Apply button or even find it for that matter.

Another larger complaint in this release

is the performance. This is not a Log Explorer problem but a architecture issue.

As I mentioned earlier, when you read the data from a transaction log,

you will experience a slowdown. The slowdown is really bad though when you try

to restore records. This is a Log Explorer issue. The performance problem when trying to salvage dropped table and truncated tables

has been improved dramatically in Log Explorer 2.1, which was in beta at the time of this review.

There’s no reason to read between the lines of this review.

Log Explorer is a must have for any developer, administrator or analyst. It will

surely pay for itself in the first few months of ownership when a user

inadvertently deletes data or if you need to audit where records came from.

Pricing:

Enterprise Edition

$995 / license

Provides the full suite of browsing, filtering, recovery and export capabilities.

Professional Edition

$395 / license

Same interface and browsing / filtering capability as Enterprise Edition but no recovery or export capabilities.

Volume discounts are available for 5+ license quantities on both editions.

Rate

Share

Share

Rate