In last three years, I presented two times on the topic Transaction Log File Architecture. During the sessions, when I discussed the undocumented command DBCC LOGINFO, one of the common questions which I got – ‘How the Initial VLF sequence number gets decided of the default transaction log file’?  In this tip, I will show you how the first VLF sequence numbers get chosen.

VLF sequence numbers

When you first create a brand new database, VLF sequence numbers of the VLFs don’t start from 1. They start at whatever the highest VLF Sequence number is of the VLFs in the Model database transaction log +1. Let’s do the DEMO to prove it!

DBCC LOGINFO ('model');
GO
CREATE DATABASE dharmendra;
GO
DBCC LOGINFO ('dharmendra');
GO

Demo Conclusion

From the demo, you can see that when I created a brand new database called “dharmendra”, the VLF sequence number of the database didn’t start from 1. It started from 34 which is highest VLF Sequence number of the model database +1.

Other Properties of the VLF

• You can see from the above image that all the VLFs doesn’t have “Sequence numbers”. Before the VLFs become active, the log management system assign the VLF Sequence number by increasing the sequence number one each time.

• Each VLF has a sequence number, which uniquely identifies it within the transaction log file.

• Once VLF sequence number has been assigned, it does not matter the VLF is active or inactive the VLF will have the sequence number.

Reference Link:

https://www.sqlskills.com/blogs/paul/category/inside-the-storage-engine/