sqlschool.gr logo


Articles of SQLschool.gr Team

Is it possible to run out of Log Sequence Numbers?

Antonios Chatzipavlis
Wednesday 01 September 2010

Πριν από λίγο καιρό στο SQL Server Magazine διάβασα το παρακάτω το οποίο είναι αρκετά ενδιαφέρον μιας και την απάντηση την έχει δώσει ένας άνθρωπος που τον έχει φτίαξει ο Paul Randal, απολαύστε το

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 may be possible to run out of log sequence numbers. What happens in that case?

A: There’s no need to worry as for all practical purposes it is not 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; 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 65536 VLFs, each one a quarter megabyte 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 65536, which is 2 to-the-power 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 65536 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 4 billion petabytes (a petabyte = 1024 terabytes) of transaction log – quite an undertaking!

Even being able to write that log out to an SSD capable of a sustained 600 MB/s, it would take 4 billion petabytes / 600 megabytes = approximately 240 million years to generate 4 billion petabytes of transaction log.

As you can clearly see – no-one’s in any danger of running out of log sequence numbers!

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.




sqlschool.gr © 2010-2024 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.