Troubleshooting SQL Server - A Guide for the Accidental DBA
Troubleshooting SQL Server - A Guide for the Accidental DBA
Article Index
Troubleshooting SQL Server - A Guide for the Accidental DBA
Chapters 2-4
Chapters 5-7
Chapters 8-9


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, 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.



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.




A Practical Approach to Large-Scale Agile Development

Author: Gary Gruver, Mike Young & Pat Fulghum
Publisher: Addison-Wesley
Pages: 208
ISBN: 978-0321821720
Audience: Newcomers to agile working on large scale projects
Rating: 4
Reviewer: Andrew Johnson

The key to this book is its subtitle, "How HP Transformed LaserJet Future Smart Firmware". But if t [ ... ]

Essential Skills for the Agile Developer

Author: Alan Shalloway, Scott L. Bain, Amir Kolsky & Ken Pugh
Publisher: Addison-Wesley, 2011
Pages: 272
ISBN: 978-0321543738
Audience: Newcomers to agile methodolgies
Rating: 5
Reviewer: Andrew Johnson

A "stop-gap" title that forms part of the  Net Objectives Lean-Agile Series. Who should rea [ ... ]

More Reviews


Last Updated ( Thursday, 23 May 2013 )

RSS feed of book reviews only
I Programmer Book Reviews
RSS feed of all content
I Programmer Book Reviews
Copyright © 2015 All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.