I have had the distinct pleasure of being asked to review a book by RedGate. The book is by a friend and respected community member Gail Shaw (blog | twitter). The book is a stairways style book and is titled “SQL Server Transaction Log Management.”
I read this book with the intent to provide an in-depth analysis and technical review. I was happy to take on the challenge of reviewing this book and happy with what I gleaned from the book.
Let’s start with my overall impression of the book. This book is ideal for every junior DBA and every accidental dba out in the workforce. If I need to repeat that statement I will, or you can flash back and reread that statement as many times as you need.
My next observation about this book is that it seems obvious that it was written with a community undertone. There are numerous references to community resources and individuals who are ever-present in the community. That community tone can be an extension of RedGate as a whole. Community appears to be fairly important to RedGate overall.
I enjoyed reading the book, and I enjoyed the demo scripts. An absolute must with this book is the demo scripts that can be downloaded and sampled (see page 13 for more code sample links). They are an absolute must to help illustrate the points that Gail and Tony are making.
I do have a minor critique that is more along the lines of readability. The first critique may be a bit zealous, but I found it difficult to read various characters such as I and 1 due to the choice of font in the paragraph text. The numeric 1 is a different font and looks like an I in paragraph versus the numeric 1 in the chapter headings and image text that actually looks like a numeric 1. There are a few cases where the font appeared to change as well making it seem disjointed. Again, that is a really a minor issue and nothing related to the technical content. An example of this font switching may be seen on Page 29 and 30.
That said, something I like to do with books such as this is try to take something of interest from each of the chapters. So, here is a list of points of interest for each chapter.
Chapter 1
Many DBAs (part-time, accidental or even full time) along with Vendors (I am adding them to this group), do not backup their databases correctly. Many in these groups “perform full backups on their databases, but they don’t perform transaction log backups” (page 21).
This book underscores that problem and helps show those groups how to implement transaction log backups and better manage their database transaction logs.
Chapter 2
Another item that many DBAs neglect to do is manage the log file fragmentation. “Log file fragmentation can degrade the performance of SQL Server processes that need to read the log ” (page 41). On some databases, I have seen this degradation cause delays in the range of 20-30 minutes or worse when bringing a database online. Do you want to take that risk?
Chapter 3
This chapter covers a topic that I see happening on a regular basis – even with “seasoned” DBAs. That topic is concerning databases that are in “pseudo-full.” This mode of operation is baffling to me. I don’t quite understand why people place a database in full recovery model and then never take a full backup of the database. The database transaction log will continue to truncate as if it were in simple recovery model.
I also particularly enjoyed reading about some really easy ways to break a transaction log chain. There are a few methods listed on how to break the chain, but more as a caution to not use them as opposed on showing you how to use them. One method is the switch from Full recovery to Simple recovery to try and control a runaway log.
One method that wasn’t mentioned ( at least not until Chapter 7) was backing up the transaction log to a NUL device. Backing up to a NUL device is just as destructive to the log chain as any method mentioned in the book.
One thing this chapter does not delve into is the implications of breaking the log chain. By Design that is covered in Chapter 7. One item that was not covered in great deal was that of log-shipping. Think of the implications of a Mirrored or Log-shipped database that is in the multi-terabyte range. Do you want to be the one to have to copy a full backup cross-country in order to rebuild log-shipping because you created a log backup to the NUL device?
Chapter 4
This chapter is summed up succinctly in the summary section of the chapter. “If you wish to measure exposure to data loss in minutes rather than hours, don’t use SIMPLE model” (page 61).
Chapter 5
Have you ever heard the expression “measure twice cut once”? In this chapter we see a subtle reminder of the importance to double-check these types of things. Did you know that records in the backupset table could be manipulated? Do you rely only the results of queries to the backupset table to confirm that backups were taken? That just might be a mistake.
This chapter shows the need to also query sys.database_recovery_status to confirm backups of the database log. If you are not double checking your log backups then please start doing so.
Chapter 6
This chapter covers Bulk Logged and some common uses for this chapter. Some concerns are also illustrated in this chapter about the use of Bulk Logged in various scenarios. There is also a discussion on pros and cons as well as Best Practice. A major concern with Bulk Logged is discussed, and for that you should read the book.
I particularly enjoyed the demo on space savings regarding the use of Bulk Logged in this chapter. Have a run at the demo and enjoy what you learn from this chapter.
Chapter 7
Besides the script on page 141 for finding open transaction information and the warnings about CDC, I found the section on Mismanagement to be an absolute gem.
There is a lot of mis-information available on the internet. The information concerning transaction logs can be quite dangerous. This section discusses some of the more prominent problems that may be found through internet searches. The debunking of some of these dangerous myths is a must read.
Chapter 8
This chapter covers some of the Golden Rules of transaction log management. A SQL Server Database is NOT a set it and forget type of venture. By extension, a transaction log is even less of a set it and forget it undertaking.
One of very first things debunked in this myth busting chapter is that concerning the use of multiple transaction logs. Simply put, don’t do it. There is an edge case where adding a second transaction log is helpful – but once the issue is resolved the additional log file MUST be removed.
One thing you may find in your internet searches is what is deemed a best practice to manage your transaction log growth explicitly. This chapter discusses that and demonstrates the benefits to manually managing your transaction logs.
In this chapter we also see a renewed discussion concerning log fragmentation. Here we see more things that are affected by a highly fragmented transaction log. To underscore the importance of proper management of your transaction log and the number of log fragments there is a very good demo script. Check it out.
Chapter 9
To top off all the great information, we conclude with something that is all too frequently overlooked by many (especially those in the earlier defined groups). You must monitor your transaction log!!
Conclusion
There is a lot of great information throughout this book. There are also some great demo scripts throughout the book.
One final note is in regards to something that I often see vendors doing. It was touched upon in this book and warned against. Please do not store backups of different types or even different databases in the same backup file. This is a practice that will almost certainly guarantee you of a larger scale disaster. What if you have one corrupt backup within that mass accumulation of backups within the same file?
I enjoyed this short book. I recommend it be on every junior or accidental DBAs bookshelf. I’d also recommend it be in every employers library for use by all IT employees.
