Search Engine Q&A #24: Why can’t the transaction log use instant initialization?

This is a quickie in response to a blog comment from my previous post on instant initialization: How to tell if you have instant initialization enabled? The comment was:

I must say, I love instant initialization. It’s awesome. But I always wondered why it’s not available for the log file. I assume there’s a technical reason… but what is it? Does it depend on having the rest of the file be zeroed out? Doesn’t it already know where it’s start and stop points are anyways, since the log is circular?

There is a lot of metadata kicking around in the Storage Engine about the transaction log (mostly in the boot page – see my post Search Engine Q&A #20: Boot pages, and boot page corruption), including where to start reading the log during crash recovery. However, there’s nothing persisted about where the end of the log is, so when a crash occurs SQL Server has to determine where the active transaction log ends (i.e. where should crash recovery stop processing log records).

The way this is done is to have each log sector have parity bits stamped on it. When the log is first created, it is zero-initialized (with zero being an illegal log sector parity value). As the log is written, each 512-byte sector in a VLF has parity bits stamped on it (initially 64). When the end of the log is reached, and it wraps around to the start of the log file, the initial VLF is reused and the parity bits are flipped to 128, so that overwritten log sectors have the new parity bits (and then for each successive reuse of a VLF the parity bits flip back-and-forth between 64 and 128). When a crash occurs, log sectors are read and processed until a log sector in a VLF is found that has the ‘old’ parity bit setting for that VLF.

This entire process will not work if there’s already random data in the space used by the log file – some of the random data could just look like a valid set of parity bits and cause the recovery system to try to process a log sector full of garbage, leading to a suspect database, at best.

All new portions of the transaction log most be zero-intialized, but only once, when the space is first added to the log file.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.