Book review: SQL Server Transaction Log Management
It was an offer I could not resist. I was promised a free copy of one of the newest books from Red Gate Books, SQL Server Transaction Log Management (by Tony Davis and Gail Shaw), with the caveat that I should write a review after reading it. Mind you, not a commercial, “make sure we sell more copies” kind of review, but a review of my actual thoughts. Yes, I got explicit permission to be my usual brutally honest self.
A total win/win for me! First, I get a free book – and free is always good, right? And second, I knew that I would get my pleasure out of it – either the pleasure of having a great read if it’s good, or the somewhat more perverse pleasure of bashing the book after struggling through it if it’s not!
For all the tl;dr types out there: this review will be mostly positive. I found no reason to give the book a bashing. And for those with an attention span beyond three paragraphs, here’s why.
Things I like about the book
The authors explicitly state that they intended the book to be lightweight and easily accessible. They clearly intended the book to be a good primer for the starting DBA who wants to know a bit more about how to manage the transaction log, but is not interested in parsing the output of sys.fn_dblog.
And they deliver as promised. The book is really an easy read. It starts with a good, basic explanation of the transaction log for DBAs who are completely new to this subject; more experienced readers can probably decide to leaf through this part without reading it all. After that, more information is stacked upon that foundation. The subjects are well organized in their own chapters. One nice touch is that the authors were not afraid to have extremely short chapters if there is not much to tell about a subject – it keeps the book well organized and information easy to find.
Aiming to keep a book simple can be dangerous when the subject isn’t. My initial concern was that the discussion of LSNs and how Virtual Log Files are (or are not) reused luckily turned out to be unfounded – Tony and Gail apparently realized that many log management scenarios really depend on an understanding of this level, so they made the effort to try to explain this subject in an easy way, without simplifying the actual mechanisms. And they succeed very well at that!
The book contains lots of code samples to illustrate the concepts. Those who like to read books while sitting at a computer can easily follow along, either by copying the queries, or by using the downloadable code samples. And for those who usually read in places where they have no computer available (I personally love reading in bed!), all relevant output of the code samples is included as well, so you don’t miss out by not running the code while reading.
Finally, for those who, after reading the book, would like to have an even deeper understanding about some of the subjects, the book contains loads of references to websites where subjects are explained in more depth.
Things I do not like about the book
It’s not all perfect, though. I did run across a few things that bothered me. Not enough to change my overall opinion of the book, but still sufficient that I feel I need to include a few words about them in my review.
First – the illustrations. The (otherwise really excellent) explanation of log internals, and how LSN and VLFs impact reuse of the log file, uses illustrations to make it easier to understand this complex subject matter. Unfortunately, these illustrations are very hard to read in the printed version of the book. Probably due to the illustrations being made in color, but the book being printed in black and white / grayscale. Pages 30 and 31 are prime examples of how not to use shading – the black text is impossible to read in the dark shaded areas. Another illustration, in chapter 6, does not suffer from this “dark on dark” problem, but it is weird to read references to “green” and “yellow” bars in a book that uses no color. Both the sloppy references to non-existent colors and (especially!) the undecipherable text in the illustrations of chapter 2 should really have been found and corrected during the pre-production process!
The desire to keep the book simple has some drawbacks as well. I am not sure if all readers would agree on this, but I think some more background would have been useful for a few specific subjects. Two questions that I am left with after this book are: “so I now know that the log is used when recovering a database – but how exactly does this undo and redo process work?”, and “okay, you made it clear that too many VLFs can slow down performance – but why, what is going on under the hood, where does this delay come from?” I do happen to know the answer to the first question myself, thanks to reading some of Kalen Delaney’s excellent books – but I feel that including these backgrounds would have helped to give the reader a better understanding of how the transaction log is used.
I also found some bits to be a bit repetitive. Maybe this was a conscious decision, to really drive home a point? Or to facilitate the readers who only read specific bits of the book instead of reading it cover to cover as I did? Or maybe it was just an oversight, or an attempt to fill the book after running out of new stuff to discuss? Anyway, on me it had the effect of making me go “Yeah, no need to repeat that yet another time, I get it now” a few times while reading.
I already said that I liked all the links to further reading. But URL shortening services like bit.ly or tinyurl exist for more reasons than just the twitter 140-character limit. I cannot click on a link in a hardcopy book, and I do not like having to manually copy a link that spans two lines in print. A missed opportunity! Also, while most links were completely optional to follow only if I wanted broader or deeper insights, there were (I think) one or two occasions where I felt that following the link was required for better understanding of the book itself – which given my habit of reading in bed was a problem. In those cases, the information should really have been included in the book.
Finally, the last chapter was a slight disappointment to me. A lot of text was spent to explain how to use and setup the various monitoring tools, which I think is a bit too far off-topic for the book. And then, after showing the reader how to set up monitoring, the failed to answer the question that I am sure haunts many incidental DBAs: “In these monitoring results, what should I look for?” Experts have very good reasons for not wanting to set specific values and thresholds in their advice, but they forget that beginners have equally good reasons for needing those values. Some more specific guidelines, with the caveat that they are not universal and not guaranteed to be still valid in future versions of SQL Server, would have made this chapter much more useful.
If you are already aware of how to allocate the transaction log, how recovery models, backups, restores, and log growth interact, and how to monitor the log; if you are looking for an in-depth discussion of transaction log internals – this is NOT the book for you. That is not a statement about the quality of the book; you are simply not the intended audience.
But for the beginning or occasional DBAs, as well as the more experienced DBAs who feel a need to deepen their understanding of the transaction log beyond the knowledge that “the bloody thing needs to be backed up or it’ll eat up my entire drive, ‘coz those Redmond guys say so”, this is an excellent read. And even if you have passed that level a long time ago, if you feel comfortable scheduling log backups, switching between recovery models, and doing a point-in-time restore – you’ll probably still learn some new stuff from reading this book.
All in all, I consider this book a recommended read. You can order it through amazon.com or through amazon.co.uk – or if you like free as much as I do, you can even download a free PDF copy (and optionally get a free evaluation copy of Red Gate’s SQL Backup Pro thrown in!)