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.