Audit SQL Server Database Activity Without the Overhead of Triggers Using Log Navigator
December 11, 2003[From SQL-Server-Performance.Com]
To many SQL Server DBAs and developers, the SQL Server transaction log file is a mystery. Sure, we know its used to maintain database integrity, but what's inside it, and is this information useful? The tools included with SQL Server don't really allow you to peak inside the transaction log to see what's there in a readable and useful form.
So if you could take a peek inside the transaction log, what would you find? In fact, you would find a wealth of information, much of it very useful, especially if you want to see, in detail, the DML (UPDATE, INSERT, DELETE) activities that have occurred in your database.
The SQL Server transaction log keeps track of every data modification performed in your database, who performed it, and when. This is a great way to see what is happening inside your database, and also provides you a way to "audit" DML activity without the need to use triggers, which are commonly used to audit transactions in many companies. While triggers are a convenient way to audit SQL Server DML activity, they also invoke a certain amount of overhead, which can hurt SQL Server performance. On the other hand, using the log for auditing eliminates the overhead of tracking DML activity.
The article continues at http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp