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.




Data Structures and Algorithms with JavaScript

Author:  Michael McMillan
Publisher: O'Reilly
Date: March 24, 2014
Pages: 246
ISBN: 978-1449364939
Print: 1449364934
Kindle: B00IV3J23Y
Audience: Intermediate JavaScript programmers
Rating: 3.5
Reviewer: Ian Elliot


A book explaining the standard data structures in the worlds mo [ ... ]

Raspberry Pi Hacks

Author: Ruth Suehle & Tom Callaway
Publisher: O'Reilly
Pages: 364
ISBN: 9781449362348
Print: 1449362346
Kindle: B00KBAS0CE

Audience: Pi enthusiasts, but not beginners
Rating: 5
Reviewer: Harry Fairhead

A total of 65 Raspberry Pi hacks - surely they have all been done before? Well yes [ ... ]

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 © 2016 All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.