This tip originally appeared on SearchWin2000.com, a sister site of SearchCIO-Midmarket.com.
Your SQL Server transaction log file is used for a broad variety of purposes. The transaction log stores data modifications to
More SQL Server resources
Most administrators deal with the mundane issues of expanding the log file when it runs out of space, and rely on the transaction log to play its roll in their everyday database operations. However, the transaction log contains a lot of information that can help you optimize your database operation, find and solve problems, check security issues and provide alerts when things go wrong. The log is the source of information for utilities and third party tools that provide services that we rely on.
For SQL Server's use as a log file in Web site applications you will find a whole host of specialized log analysis tools that excel in uncovering hit patterns, usage trends, and other data that Web administrators want to know about. This is also the case for many specific performance analyses. General log analysis tools are somewhat rarer however.
One general tool that runs on a wide variety of platforms and analyzes a broad spectrum of log files is Sawmill. Sawmill isn't specific to SQL Server, but it can work with SQL Profiler data to analyze trends. Like any good log analytical tool Sawmill has a reporting and graphing function for you to work with.
If you want a tool that is more specific to SQL Server than you might want to look at Lumigent's Log Explorer for SQL Server, an analysis and data recovery tool that mines the information in your SQL Server transaction long. Among the information that it can help you with are determining who has changed records and tables, a common security issue. You can also use this tool to search for user and application events, and to provide e-mail notification when a specific event occurs.
Among the events that can generate an alert in the current version of Log Explorer (v. 4.0) are CREATE and DROP databases; CREATE, ALTER, and DROP objects or TEMP tables; ROLLBACK operations, access of the Database Console Command (DBCC), a backup or restore of a database, and a deadlock event. Recovery is the most important use of the transaction log, and Log Explorer provides a means to not only find modified, deleted, truncated or dropped data, but to selectively undo these changes on an operation by operation basis or as a set of several transactions. You can restore tables completely, if necessary and in the latest version of the program do these operations on a user or login name basis Events that can be undone or redone are functions, stored procedures, triggers, and view. Also, Log Explorer provides an export file that lets your transactional analysis be viewed in other tools for further study.
Barrie Sosinsky is president of consulting company Sosinsky and Associates in Medfield, Mass. He has written extensively on a variety of computer topics. His company specializes in custom software (database- and Web-related), training and technical documentation. Let us know what you think about this tip; email firstname.lastname@example.org.
This was first published in December 2004