Q: I’ve been wondering about the algorithm for generating log sequence numbers for transaction log records, and I’m concerned that with a high enough workload it might be possible to run out of log sequence numbers. What happens in that case?
A: There’s no need to worry because for all practical purposes it isn’t possible to run out of log sequence numbers. As a bit of background, a log sequence number is a three-part number used to uniquely identify a transaction log record—constructed from the sequence number of the virtual log file (VLF) containing the log record, the log block number within the virtual log file, and the log record number within the log block.
This isn’t really important, but what is important is that the VLF sequence number is a 64-bit number. Whenever a VLF is reused in the transaction log, the VLF sequence is increased by 1. So let’s do a little math.
Imagine a transaction log with 65,536 VLFs, each 1/4MB in size (not a nonsensical situation, depending on how your transaction log is being managed—for more details on this and on transaction log internals, see “Importance of proper transaction log size management." Each time the log is completely used and wraps around to the start, the VLF sequence number will increase by 65,536, which is 2 to the power of 16 (2^16).
A 64-bit number can support 2^64 values. To be able to exhaust the 2^64 possible VLF sequence numbers, our example transaction log would have to wrap 2^64 / 2^16 = 2^48 times. That’s a lot of log wrapping. But how much transaction log does that equate to?
Our example log is 65,536 x 1/4MB in size, which is 16GB. To wrap that log 2^48 times, you’d need to generate 2^48 x 16GB of transaction log, which equates to four billion petabytes (a petabyte = 1024 terabytes) of transaction log—quite an undertaking!
Even being able to write that log out to a solid-state drive (SSD) capable of a sustained 600MBps, it would take four billion petabytes / 600 megabytes = approximately 240 million years to generate four billion petabytes of transaction log. As you can clearly see, no one’s in any danger of running out of log sequence numbers!
