Author: Jonathan Kehayias & Ted Krueger
Publisher: Red Gate Books, 2011
Pages: 370
ISBN: 978-1906434786
Audience: Troubleshooting DBAs and Developers
Rating: 4.7
Reviewer: Ian Stirk

Chapter 8 Large or Full Transaction Logs

The chapter discusses common database transaction log problems, index maintenance, full logs, and long running transactions. The chapter highlights perhaps the biggest misconception about a database backup: that a database backup also backs up the transaction log –it does not! You need to back up both the database and the log separately, and regularly (assuming the recovery model is set to FULL).

How the transaction log works is explained in sufficient detail, with reference to Write Ahead Log (WAL), REDO and UNDO. The transaction log is written to first, data is written to disks later using checkpoints and the lazy writer processes. The log is largely sequential so should have its own disk (so the disk head doesn’t have to spend time repositioning). Log truncation is discussed with reference to Virtual Log Files (VLF), active and uncommitted transactions. The importance of sizing and growing the log is explained.

There is an interesting section on diagnosing a runaway log, where creating or altering a large index is a common cause. Checking the log_reuse_wait_desc column in sys.databases often informs you of problems with the log. It is alarming to discover the most common reason the log is full is because a log backup has never been taken! Long running transactions can also cause problems, often the solution is to break the transaction into smaller pieces of work (use TOP xx). You can discover any open transactions by running DBCC OPENTRAN inside the relevant database.

There’s pragmatic advice on what to do when the transaction log is full, especially when there’s no space available to take a log backup (i.e. change to SIMPLE mode, then back to FULL and take a backup) – but hopefully people will learn from this, there is no substitute for a solid backup strategy. And of course, you should regularly check the backups taken are viable (there’s a helpful tool from RestoreChecker.com, and a free version too!)

As a chapter about problems with the transaction log, it is wide ranging, and detailed. For me, it felt a bit out of step with the previous chapters. 

Chapter 9 Truncated tables, dropped objects and other accidents waiting to happen

Even the most experienced database professionals can make mistakes, e.g. dropping objects. The point is to learn from your mistakes and put procedures in place so the mistakes are more difficult to make (e.g. have one account for your standard work and a rarely used higher privileged account).

The chapter starts with a discussion of disappearing objects, recovery, accidents, write access, and the acceptable level of data loss (e.g. 2 hours). The key to preventing data loss is to have a solid backup strategy in place.

Recovering lost data is discussed, both where a backup is available (use marked transactions , a given point in time, or the log sequence number), and unavailable (use log recovery tools or secondary/replica database).

You’ll want to find the cause of the dropped object, looking inside the default trace provides details of deleted objects. Actually the default trace is useful for lots of other things including objects (created, amended, deleted), errors and warning, security audit and more.

The chapter ends with a discussion of prevention being better than cure. There is an emphasis on the need to plan for recovery from data loss. The importance of having a change control process, and an appropriate security model, is explained. This is followed by a miscellany of good practices (use windows logins, table access via sprocs only, use database roles, use triggers to prevent/ log changes to objects and data).

This is a wide ranging comprehensive chapter, well tied together, with good links to additional material.

Conclusion

This free eBook provides solutions to the most common problems encountered by the inexperienced (accidental) DBA. There’s an emphasis on performance and troubleshooting, unlike related books this one is organized by problem area. The book is useful for all levels of experience, being a great refresher for the experienced DBA and a welcomed introduction for the inexperienced DBA.

This book provides in one place, up-to-date information on common problems and their solution. With its wide range and great depth it is highly recommended.