Initial VLF sequence numbers and default log file size

We're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one.

Here's an example:

CREATE DATABASE foo;
GO
DBCC LOGINFO ('foo');
GO

FileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
——- ——— ———– —— ——- ——- ——————
2       253952    8192        137    2       64      0
2       253952    262144      0      0       0       0

Why is the first sequence number (FSeqNo) 137 and not 1? (For an explanation of the various fields in the output of the undocumented DBCC LOGINFO, see the post Inside the Storage Engine: More on the circular nature of the log.)

The answer is that it's one more than the highest VLF sequence number currently in the model database. Let's see:

DBCC LOGINFO ('model');
GO

FileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
——- ——— ———– —— ——- ——- ——————
2       253952    8192        136    2       128     0
2       262144    262144      135    0       128     0

The highest FSeqNo in the output above is 136, which accounts for the 137 in the new user database.

Now I'll prove it by increasing the sequence number in the model database:

USE model;
GO
CREATE TABLE BigRows (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
GO
INSERT INTO BigRows DEFAULT VALUES;
GO 50
DBCC LOGINFO;
GO

FileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
——- ——— ———– —— ——- ——- ——————
2       253952    8192        136    0       128     0
2       262144    262144      137    2       128     0

And now I'll create another user database and the first VLF sequence number should be 138:

CREATE DATABASE foo2;
GO
DBCC LOGINFO ('foo2');
GO

FileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
——- ——— ———– —— ——- ——- ——————
2       253952    8192        138    2       64      0
2       360448    262144      0      0       0       0

And it is – proven!

The eagle-eyed among you may notice that the log file size of the database foo2 I just created is larger than the size of the foo database I initially created. This is because the default log file size is the higher of 0.5MB or 25% of the sum of the data files created. In this case, the data file is copied from the model database, and I made it larger when I created the BigRows table, so the log file of the new database has to be slightly larger.

One question that came up in the post comments – why is the highest VLF sequence number in my model database 136? No particular reason except I've done a bunch of things in model which generated transaction log and so the VLF sequence numbers have increased. It's almost certain to be different in your SQL Server instances for that reason.

Next up tomorrow – results from the index count survey from last month.

12 thoughts on “Initial VLF sequence numbers and default log file size

  1. Thanks Paul.

    Just out of curiosity is the FSeqNo 136 a default number for the model. You answered the question why did the VLF sequence number for a new database start at 137 so now I have to ask why did the model number start at 136 (or so it appears).

    Jeff

  2. Hi Paul,
    one question out of curiosity, i have read minimum size of transaction log has to be 512 kb and it will have 2 VLF with minimum size of 256 kb, but in above example foo database has 2 VLF with size 253952 bytes i.e. about248 kb which is less than 256 kb, why is so.

  3. And what is the significance of Startoffset column?

    You dint mention that in “Inside the Storage Engine: More on the circular nature of the log” blog also..

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.