Analyzing your SQL Server log files

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

    Requires Free Membership to View

your database which includes all inserts, deletes and updates. Data in the transaction log is used to commit data when a multistep change is approved, or rollback changes when you need to deal with a disaster recovery situation where you need to prevent data loss.

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 editor@searchcio-midmarket.com.

This was first published in December 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.