patternsqlModerate
Is It Possible to Run Out of Log Sequence Numbers?
Viewed 0 times
lognumberssequencepossibleoutrun
Problem
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?
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?
Solution
I found the following answer from a post written by Paul Randal.
Is It Possible to Run Out of Log Sequence Numbers?
Paul writes:
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!
Is It Possible to Run Out of Log Sequence Numbers?
Paul writes:
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!
Context
StackExchange Database Administrators Q#195076, answer score: 13
Revisions (0)
No revisions yet.