Reading the Log


Although the log contains a record of every change made to a database, it is not intended to be used as an auditing tool. The transaction log is used to enable SQL Server to guarantee recoverability in case of statement or system failure and to allow a system administrator to take backups of the changes to a SQL Server database. If you want to keep a readable record of changes to a database, you have to do your own auditing. You can do this by creating a trace of SQL Server activities, using SQL Server Profiler or the server-side trace capability.

You might be aware that some third-party tools can read the transaction log and show you all the operations that have taken place in a database and can allow you to roll back any of those operations. One such tool is Lumigent's Log Explorer. (You can download a trial version from the Lumigent Web site at http://www.lumigent.com/downloads/.)

The developers of the original Log Explorer product, called Log Analyzer, did have access to an undocumented DBCC command for reading transaction log records, but even that was not enough information. They spent tens of thousands of hours looking at byte-level dumps of the transaction log files and correlating that information with the output of the undocumented DBCC command. Once they had a product on the market, Microsoft started working with them, which made their lives a bit easier in subsequent releases. The Lumigent engineers now spend several weeks in the labs at Microsoft before each new release and are given access to quite a bit of very low-level internal information about the structure of the log records. In return, Lumigent develops an internal document that other third-party vendors can potentially access.

Although you might assume that reading the transaction log directly would be interesting or even useful, it's just too much information. If you know in advance that you want to keep track of what your SQL Server is doing, you're much better off defining a trace with the appropriate filter to capture just the information that is useful to you. Otherwise, using a product such as Lumigent's Log Explorer to save yourself all the research and testing hours could be a real bargain.



Inside MicrosoftR SQL ServerT 2005. The Storage Engine
Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
ISBN: 0735621055
EAN: 2147483647
Year: 2004
Pages: 115
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net