A couple of questions and comments on the Ask.SQLServerCentral.com website have shown some misunderstanding about the use of SQL Server’s Transaction Log files. At about the same time, I was approached by Red Gate as part of their Friends programme to review their book “SQL Server Transaction Log Management“. Nice timing! Most of Red Gate’s books on SQL Server are available as free PDFs; unfortunately, this isn’t (yet) the case for this book…
And so, on with the review.
Chapter 1: Meet the Transaction Log
This first chapter covers a lot of ground – including brief mention of the important point that, although it is possible to have multiple physical log files, because SQL Server writes them sequentially rather than a page here and a page there, like it does with the data files,
…there is no advantage to having multiple files from the perspective of log throughput.
That point is made so early on, in fact, that it’s possible that the average skim-reader might miss it. And that’s a key factor with this book – there’s a lot of information in a relatively short (200-ish) page book, and this first chapter makes use of forward references. So go carefully.
Chapter 1 also covers how the transaction log is used by SQL Server (remember, it’s not just for data manipulation changes, but for data definition changes, such as index rebuilds), how to back it up & restore it, how to manage the size of the log file and how SQL Server organizes the log with VLFs.
Chapter 2: Some, But Not Too Much, Log Internals
Not too much? Let’s see… here we have discussion on VLFs (Virtual Log Files, the sections of your TLog file), LSNs (Logical Sequence Numbers), and how your transaction log backup uses these to backup the appropriate sections of the transaction log.
There’s a useful script that walks you through how these concepts hang together, making (as expected) liberal use of
DBCC Loginfo to show the VLF information. This then leads onto a description of Log Fragmentation caused by inappropriate autogrowth settings.
Chapter 3: Transaction Logs, Backup and Recovery
At last! Discussion of backup/recovery models and how they affect the transaction log, including a reminder that a “full” backup doesn’t include the information from the logs…
It’s nice to see that, as well as pimping their own books and tools, they also discuss Ola Hallengren’s most excellent SQL Server maintenance scripts – and fail to mention that Ola has written these scripts in such a way that they can take backups using some third-party backup tools such as Red Gate’s SQL Backup. Another good thing about Ola’s scripts? They’re free.
Chapter 4: Managing the Log in SIMPLE Recovery Model
A very short chapter! Just remember, a database not in SIMPLE recovery is treated as such until the first FULL database backup is taken. Or, to look at it another way, you can’t do T-Log backups until a FULL backup has been made.
Chapter 5: Managing the Log in FULL Recovery Model
So this is the big one. What gets logged, minimally logged operations, Log backups (including tail log backups). And on to restore and recovery options, including point-in-time restores. Lots of scripts to show the various options.
Chapter 6: Managing the Log in BULK LOGGED Recovery Model
Amusingly (to my mind) this chapter is longer than the FULL chapter that went before… Which is entirely appropriate.
The chapter is a discussion of the
BULK LOGGED recovery model, its limitations, the risks associated with it, and a summary the situations where it can be advantageous – a useful overview. There are comparisons between FULL and BULK LOGGED database logs, neatly scripted and explained. The Advantages / Disadvantages are further explained particularly with reference to crash/restart recovery and database restores. The chapter also contains a section on the best practices for use of the BULK_LOGGED recovery model (complete with a useful scenario to see how it works).
Chapter 7: Dealing with Excessive Log Growth
This is the one that crops up regularly on the Q&A sites such as Ask.SQLServerCentral. As well as looking at how to deal with the problem, there’s a discussion on how to size the log file in the first place – this may be something that needs to be revisited as your database usage changes.
There’s a major section on “Runaway Transaction Logs”, looking at several of the possible causes and how to mitigate these problems, such as switching to BULK_LOGGED mode when doing Index rebuilds. There’s also a discussion on how to investigate expensive / log-heavy transactions, complete with handy scripts to help identify these.
…the most common cause of a full or large transaction log, namely operating a database in the FULL recovery model (or less common, the BULK_LOGGED recovery model), without taking transaction log backups
Ah, if only I had a penny for every time I’ve seen that problem in the Q&A forums… Wait. I still wouldn’t have a fiver.
And why does this happen? As discussed in the book, because (generally), the
model database (that acts as a template for all databases created on the server) is, by default, in FULL recovery model, and the inexperienced DBA hasn’t realised that the FULL backup only deals with data, and not transaction logs…
Other transactional options (long-running / active transactions, uncommitted transactions) are discussed at length, and there’s shorter discussion of some of the other possible causes (replication, active backups, mirroring), before moving into how to handle a full TLog that shows the wrong ways (some of which hadn’t occurred to me) & the right way, or “Proper Log Management”.
Chapter 8: Optimizing Log Throughput
This chapter concentrates on the architecture of your system to make sure that your transaction logs are running nice and quickly, and a part of that is making sure that the size and growth settings are properly configured. So there’s a discussion on this, on VLFs, Instant File Initialisation (tip: TLog files don’t do IFI), sizing, recovery / undo timings etc.
Chapter 9: Monitoring the Transaction Log
A quick look at keeping an eye on your T-Log using monitoring tools usch as PerfMon or Red Gate’s SQL Monitor, DMOs, T-SQL & PoSh scripts.
That’s all, folks
A lot of information is presented in this compact book, and there are plenty of scripts, and links to further articles to read for deeper knowledge.