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.