How the Initial VLF sequence number gets decided of the default transaction log file

, 2017-06-28 (first published: )

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/

 

The post How the Initial VLF sequence number gets decided of the default transaction log file appeared first on .

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads