Search results matching tags 'transaction log' and 'internals'

Geek City: What Triggered This Post?

Kalen Delaney — Tue, 01 Jan 2013 00:00:00 GMT

I’d really like to get another post up onto my much neglected blog before the end of 2012. This will also start one of my New Year’s resolutions, which is to write at least one blog post a month. I’m going to tell you about a change in SQL Server that wasn’t announced in any “What’s New” list that I ever saw, perhaps because it was just a chance in internal behavior, and nothing that required any change in user applications.

Do you retest what you know is true for every new version? When I update my books, I do test all the scripts, but if there isn’t a script, I don’t retest every ‘fact’ that I have known for years is true. And sometimes, things change. And sometimes my reviewers notice those unreported changes, and sometimes they don’t.

You might be aware of the fact that SQL Server can perform UPDATE operations in two different ways. The UPDATE can be performed as a two-step process: delete the old row and then insert a whole new row, or, the UPDATE can be performed (much more efficiently) as an update-in-place. When the two-step UPDATE is performed, it is a LOT more work. Not only does SQL Server have to log the entire old row and the entire new row, but each nonclustered index is also modified twice, and each of those index changes also has to be logged. So it’s nice when an update-in-place is done, because only the bytes changed are logged, and only indexes on the updated columns are affected.

Prior to SQL Server 7, there were actually four different ways that UPDATE could be done. The two-step UPDATE had some variations that could make it even slower in some cases! But that was a long time ago, so I’m not going to go into the details now. But I will say that back then, in order to get an update-in-place to occur, there was a big long list of prerequisites that had to be met and if you missed just one, you’d get one of the slower UPDATE operations.

As of SQL Server 7, update-in-place became the default. The only time it doesn’t happen is when the row can’t stay in the same location (such as when you update a clustered index key column) or when SQL Server really needs the old and new versions of the row.

In SQL 7, one of the places that SQL needed the old and new version of the updates rows was when processing triggers. Triggers need the transaction log to get the contents for the DELETED and INSERTED pseudo-tables. And because triggers needed the entire old and new versions of the updated rows, the UPDATE was performed as a two-step operation. DELETE the old row, log the entire old row, and the INSERT the new row with the new values, and log the entire new row.

But as of 2005, we now have the version store, primarily used for SNAPSHOT isolation, but available for other uses as well. In SNAPSHOT isolation, the version stores stores ‘old versions’ of rows that have been updated or deleted. I knew that the version store was also used for triggers, but it only occurred to me just recently that maybe, because the old and new versions of the row were not needed from the log, perhaps UPDATEs did not always need to be performed internally as a two-step UPDATE.

So I decided to test it out.

-- DEMO: If there is an UPDATE trigger, are updates logged as DELETE + INSERT?
-- First build a new database.

USE master;
GO
IF (SELECT db_id('TestTrigger')) IS NOT NULL
DROP DATABASE TestTrigger;
GO
CREATE DATABASE TestTrigger;
GO
ALTER DATABASE TestTrigger SET RECOVERY SIMPLE;
GO
SELECT db_id('TestTrigger');
GO

USE TestTrigger;
GO

-- Just for a warmup, look at the function fn_dblog, which works in the current database

SELECT * FROM fn_dblog(null, null);
GO

-- Create a new table to work with
IF (SELECT object_id('objects')) IS NOT NULL
DROP TABLE objects;
GO
SELECT TOP 100 * INTO objects FROM sys.objects;
GO

-- Create a clustered index on the table
CREATE CLUSTERED INDEX objects_clustered on objects(name);
GO

-- First examine an update we know is NOT done in place,
-- i.e. updating a clustered key value

UPDATE objects SET name = 'newrowsets' WHERE name = 'sysrowsets';
GO

-- Look at last 10 rows; notice a LOP_DELETE_ROWS and LOP_INSERT_ROWS
-- The AllocUniteName column shows the object affected is the clustered index on dbo.objects
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Now examine an update we know is done in place,
-- i.e. updating an unindexed column on a table with no triggers
UPDATE objects SET parent_object_id = 1 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW on the dbo.objects allocation unit
SELECT Operation, [Transaction ID], AllocUnitName FROM fn_dblog(null, null);
GO

-- Create an update trigger
-- Will the update be done with the siple LOP_MODIFY_ROW or with the LOP_DELETE_ROWS and LOP_INSERT_ROWS
CREATE TRIGGER trg_update_objects ON objects FOR UPDATE
as
SELECT * FROM DELETED; SELECT * FROM INSERTED;
RETURN;
GO

-- Now perform update again
UPDATE objects SET parent_object_id = 10 WHERE name = 'sysfiles1';
GO

-- Look at last 3 rows; notice a LOP_MODIFY_ROW
SELECT * FROM fn_dblog(null, null);
GO

Since the database is in SIMPLE recovery model, you can issue a CHECKPOINT before each UPDATE if you want to reduce the number of rows in the log to make it easier to examine.

So it seems that I need to update my course and some of my writings. There might also be special cases that still require that an two-step UPDATE be performed in the presence of triggers, but it seems like a two-step UPDATE is not ALWAYS required anymore. That is very good news!

I hope you all have a wonder-filled and joyous New Year!

~Kalen

Page splits, extended events, index page allocation and all the fish

mz1313 — Mon, 25 Apr 2011 19:49:45 GMT

2 years ago I wrote about monitoring page splits with Extended Events. Only 2 bloggers explored Extended Events feature at that time, so my post was more of a learning than for any practical matter. Yet one question remained open: according to straightforward calculations, after 3 splits of the first page, next split should have occurred on another page - but trace with extended events clearly showed 4 splits of initial page.

Recently Pavel Nefyodov has drawn my attention to another curious fact: initially we had 1 page; after all those splits we had 10. But trace showed 10 splits, not 9. So where did additional split come from? And finally I've realized that there is another simple way to trace those splits - via transaction log. If we manually checkpoint the system before and perform insert in transaction afer - our transaction log will containg exactly the relevant data; and it'll remain this way all the time transaction stays open. We'll query transaction log using undocumented but widely known fn_dblog function.

OK, Let's see some code (mostly taken from my old post which in turn used page split demo from Kalen Delaney's blog). Initial configuration (including trace definition):

USE tempdb; GO CREATE TABLE split_page (id INT IDENTITY(0,2) PRIMARY KEY, id2 bigint DEFAULT 0, data1 VARCHAR(33) NULL, data2 VARCHAR(8000) NULL); GO INSERT INTO split_page DEFAULT VALUES; GO 385 CREATE EVENT SESSION MonitorPageSplits ON SERVER ADD EVENT sqlserver.page_split ( ACTION (sqlserver.database_id, sqlserver.sql_text) WHERE sqlserver.database_id = 2 ) ADD TARGET package0.asynchronous_file_target ( SET filename = N'c:\temp\MonitorPageSplits.etx', metadatafile = N'c:\temp\MonitorPageSplits.mta' ); GO CHECKPOINT GO

Short glance at our table's allocation inside data file:

DBCC IND(tempdb, split_page, -1); GO

PagePID	IndexID	iam_chain_type	PageType	IndexLevel
175	1	In-row data	10	NULL
148	1	In-row data	1	0

So we have 1 IAM page and 1 data page. Now the split itself and - remember, we should query transaction log before committing transaction if we don't want accidental checkpoint to ruine all our efforts.

ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = start; GO SET IDENTITY_INSERT split_page ON; GO BEGIN TRAN INSERT INTO split_page (id, id2, data1, data2) SELECT 111, 0, REPLICATE('a', 33), REPLICATE('b', 8000); GO SET IDENTITY_INSERT split_page OFF; GO SELECT [Current LSN], Operation, Context, [Page ID], [Description] FROM fn_dblog(NULL, NULL) COMMIT GO ALTER EVENT SESSION MonitorPageSplits ON SERVER STATE = STOP; GO DROP EVENT SESSION MonitorPageSplits ON SERVER; GO SELECT split.value('(/event/data[@name=''file_id'']/value)[1]','int') AS [file_id], split.value('(/event/data[@name=''page_id'']/value)[1]','int') AS [page_id], split.value('(/event[@name=''page_split'']/@timestamp)[1]','datetime') AS [event_time], split.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [sql_text] FROM ( SELECT CAST(event_data AS XML) AS split FROM sys.fn_xe_file_target_read_file('c:\temp\MonitorPageSplits*.etx', 'c:\temp\MonitorPageSplits*.mta', NULL, NULL) ) AS t ORDER BY [event_time]

First let's take a look at our table now:

PagePID	IndexID	iam_chain_type	PageType	IndexLevel
175	1	In-row data	10	NULL
148	1	In-row data	1	0
184	1	In-row data	2	1
185	1	In-row data	1	0
187	1	In-row data	1	0
188	1	In-row data	1	0
189	1	In-row data	1	0
190	1	In-row data	1	0
191	1	In-row data	1	0
392	1	In-row data	1	0
393	1	In-row data	1	0
394	1	In-row data	1	0

Transaction log contains more rows than we are interested in, so I cleaned irrelevant ones out from the table below and correlated transaction log with extended events trace. I also transformed hexadecimal page numbers from transaction log to decimal ones - like we see in extended events trace.

So we can see that indeed there have been 3 splits of the first page. From transaction log we see that first action was actually allocating of page 184 which is - look at the allocation table above - index page. According to transaction log this page is allocated - not splitted from any other page. Still extended events trace shows as if it has been splitted from the first data page of our table. Don't know whether this behavior can be considered as bug - probably not. Anyway - both unanswered question from my old post are resolved: first page indeed has been splitted 3 times and we had only 9 data page splits while 10th split belongs to index page allocation.

Have a nice week!

Geek City: What gets logged for SELECT INTO operations?

Kalen Delaney — Tue, 15 Mar 2011 19:41:00 GMT

Last week, I wrote about logging for index rebuild operations. I wanted to publish the result of that testing as soon as I could, because that dealt with a specific question I was trying to answer. However, I actually started out my testing by looking at the logging that was done for a different operation, and ended up generating some new questions for myself.

Before I started testing the index rebuilds, I thought I would just get warmed up by observing the logging for SELECT INTO. I thought I knew what got logged, but I was wrong. My understanding was that as a minimally logged operation, in FULL recovery, SELECT INTO would log each 8K page as it was written, not each row like a normal INSERT would. In BULK_LOGGED, SELECT INTO would create a log record for each page, but it would not log the entire 8K page. And of course, it would also not log every row. However, my first test turned up some surprising results.

Here's what I did: First, I made sure the AdventureWorks database was in FULL recovery, and dropped the Details table if it existed. Then I created a table called SELECTINTOLoggingData to hold the results of calling fn_dblog. As in last week's script, the following information was captured:

Max log record size
Sum of log record sizes
Number of log records
Number of log records larger than 8K

Finally, I backed up the AdventureWorks database to give me a foundation for log backups. So here's the first part of the script:

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
USE AdventureWorks;
GO
IF EXISTS (SELECT 1 FROM sys.tables
            WHERE name = 'Details')
       DROP TABLE Details;

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'SELECTINTOLoggingData')
    DROP TABLE SELECTINTOLoggingData;
GO

CREATE TABLE SELECTINTOLoggingData
(DBRecModel CHAR(12), MaxLogRowSize INT, NumLogRows INT, NumLargeRows INT);
GO

BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\AW.bak' WITH INIT;
GO

The actual testing was be done first in FULL recovery, then in BULK_LOGGED. I backed up up the transaction log, then created the Details table by executing a SELECT INTO from the Sales.SalesOrderDetail table. The Details table has 1495 pages and 121,317 rows. Finally, I inserted the relevant values from fn_dblog into the SELECTINTOLoggingData table.

---- FULL Recovery

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO
SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO

INSERT INTO SELECTINTOLoggingData
SELECT 'FULL',
        MAX([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] >8000)
        FROM fn_dblog(null, null);
GO

DROP TABLE Details;
GO

-- BULK_LOGGED Recovery

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO

INSERT INTO SELECTINTOLoggingData
SELECT 'BULK_LOGGED',
        MAX([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] >8000)
        FROM fn_dblog(null, null);
GO

Finally, I looked at the data I collected:

SELECT * FROM SELECTINTOLoggingData;
GO

Here are my results:

Although the number of large (8K) log rows was as expected, I was quite surprised to see the total number of log records! (And yes, I know the column names use the term "Log Rows", but I'm calling them "Log Records". I guess I just wanted a shorter column name.)

I then ran the same test on a different version of the Sales.SalesOrderDetail table that I use in some of my class examples. Here is the script to create the Sales.SalesOrderDetail2 table:

CREATE TABLE Sales.SalesOrderDetail2 (
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL ,
CONSTRAINT [PK_SalesOrderDetail2_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
GO

INSERT INTO Sales.SalesOrderDetail2 SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
FROM AdventureWorks.Sales.SalesOrderDetail;
GO

After setting the AdventureWorks database back to FULL recovery, deleting the Details table and truncating the SELECTINTOLoggingData table, I run the same test as above (starting at the "Full Recovery" comment, substituting Sales.SalesOrderDetail2 for Sales.SalesOrderDetail.

ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
DROP TABLE Details;
GO
TRUNCATE TABLE SELECTINTOLoggingData;
GO

Here are my new results:

So these are much more like the results I expected from a SELECT INTO operation!

Why did the first example, selecting from Sales.SalesOrderDetail, do so much more logging?

It turns out that if you have a IDENTITY column in the source table, SELECT INTO must then log every row as the IDENTITY value is generated.

Does this mean you should rethink the beneift of using IDENTITY columns? Only if you're doing a lot of SELECT INTO operations to copy a table with IDENTITY columns. Normal INSERT statements, which is how IDENTITY values are usually generated, are going to have the same amount of logging whether or not the table has an IDENTITY column. (Let me know if you think otherwise.)

So now you know.

~Kalen

p.s. I would like to thank Sunil Agarwal for his brilliant insights and encouragement. Thanks for everything, Sunil!

Geek City: What gets logged for index rebuild operations?

Kalen Delaney — Tue, 08 Mar 2011 18:54:00 GMT

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class in June asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help.

I knew the difference between ALTER INDEX in FULL vs BULK_LOGGED recovery when doing normal OFFLINE rebuilds, but I wasn't sure about ONLINE. So I dug out some old scripts, massaged them a bit, and ran some tests.

A minimally logged operation is one that does not always log every single row. In FULL mode, SQL Server will log the entire page as each page is filled, so that we end up with individual log records of over 8K. In BULK_LOGGED mode, SQL Server just logs the fact that a new page was generated (and some information about the page) and doesn't log the full 8K. So the log space used is quite a bit less.

I will use Microsoft's AdventureWorks database that you can download from codeplex. I used the SQL Server 2005 version.

After making sure the database is set to FULL recovery, I make a copy of the Sales.SalesOrderDetail table (which I call Details) and build a clustered index on it. The table has about 1580 pages and 121,317 rows.

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO
USE AdventureWorks;
GO
IF EXISTS (SELECT 1 FROM sys.tables
            WHERE name = 'Details')
       DROP TABLE Details;


SELECT * INTO Details
FROM Sales.SalesOrderDetail;
GO

CREATE CLUSTERED INDEX CL_details_indx ON Details(SalesOrderID);
GO

I then build a table to store log information that I will gather after the different index rebuild operations. After that I back up the database to make sure I have a base for making log backups.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'AlterIndexLoggingData')
DROP TABLE AlterIndexLoggingData;
GO

CREATE TABLE AlterIndexLoggingData
(DBRecModel CHAR(12), IndexBuildMode CHAR(3), MaxLogRowSize INT, SumLogSize BIGINT, NumLogRows INT, NumLargeRows INT);
GO
BACKUP DATABASE AdventureWorks TO DISK = 'c:\backups\AW.bak' WITH INIT;
GO

Now starts the testing. There are four sections, each of which basically does the same thing, with the four permutations of FULL vs BULK_LOGGED recovery, and ONLINE vs OFFLINE index builds. Each section backs up the transaction log to truncate it, and then reports the number of rows in the log, just to verify we really have (logically) cleared it out. It then performs a rebuild of the index on the Details table. Finally, it captures the following information from the log using fn_dblog:

Max log record size
Sum of log record sizes
Number of log records
Number of log records larger than 8K

---- FULL Recovery, ONLINE = OFF

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO
ALTER INDEX CL_details_indx ON Details REBUILD
GO

INSERT INTO AlterIndexLoggingData
SELECT 'FULL', 'OFF',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO

--- FULL Recovery, ONLINE = ON

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

ALTER INDEX CL_details_indx ON Details REBUILD
WITH (ONLINE = ON)
GO

INSERT INTO AlterIndexLoggingData
SELECT 'FULL', 'ON',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO
--SELECT * FROM AlterIndexLoggingData;
GO

--- BULK_LOGGED Recovery; ONLINE = OFF

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

ALTER INDEX CL_details_indx ON Details REBUILD
GO

INSERT INTO AlterIndexLoggingData
SELECT 'BULK_LOGGED', 'OFF',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO
--SELECT * FROM AlterIndexLoggingData;
GO

--- BULK_LOGGED Recovery, ONLINE = ON

BACKUP LOG AdventureWorks TO DISK = 'c:\backups\AW_log.bak' WITH INIT;
SELECT COUNT(*) FROM fn_dblog(null, null);
GO

ALTER INDEX CL_details_indx ON Details REBUILD WITH (ONLINE = ON)
GO

INSERT INTO AlterIndexLoggingData
SELECT 'BULK_LOGGED', 'ON',
        MAX([Log Record Length]),
        SUM([Log Record Length]),
        COUNT(*),
        (SELECT COUNT(*) FROM fn_dblog(null, null) WHERE [Log Record Length] > 8000)
        FROM fn_dblog(null, null);
GO
SELECT * FROM AlterIndexLoggingData;
GO

Here are my results:

Notice the following:

Only FULL recovery with an OFFLINE rebuild logged the 8K pages.
FULL recovery with an ONLINE rebuild actually did log every single row; in fact, it looks like at least two log records were generated for each row. There is a LOT of logging for this set of options and by far, the largest amount of log space used.
BULK_LOGGED mode used substantially less log space than FULL, but the difference between ONLINE and OFFLINE is even more pronounced. In FULL recovery, ONLINE used about twice the log space, in BULK_LOGGED, the logging required more than 8 times the space!

I'm sure there are more fun facts you can pull out of the results from fn_dblog, but for my first look at the logging differences between ONLINE and OFFLINE index rebuilds, this is what I wanted to know. It's not a complete answer to the student's question, but it does indicate there is a lot more logging with ONLINE index builds, so it should not be surprising if there are a lot more log write waits. And I would assume that switch to BULK_LOGGED recovery could make a noticeable difference.

I hope this is useful!

~Kalen

Geek City: Exploring the Transaction Log Structure

Kalen Delaney — Tue, 22 Dec 2009 00:48:00 GMT

As I mentioned last October, my PASS preconference seminar was a whole day about the transaction log. I told the attendees all kinds of things about the structure of the log, and told them about one of the best tools for exploring the structure: the command DBCC LOGINFO. This command is officially undocumented, but numerous bloggers have discussed it in some detail, including here. I'm not going to go into all the details here, but I'll be writing some related blog posts over the next month or so. To understand DBCC LOGINFO you need to know about VLFs (Virtual Log Files) and one of the best places to read about VLFs is in Paul Randal's TechNet article. The DBCC LOGINFO command returns one row per VLF including the following columns:

FileId

If you only have one physical log file, this value will be the same in every row. If you have multiple log files, you can use this value to explore the order that the SQL Server will fill up VLFs in multiple files. (More details in a future post.)

FileSize

This value is in bytes. If you have your log file growth set to a percentage (which, unfortunately is the default), you should notice this value getting progressively larger as more VLFs are added to your log file.

StartOffset

This value is also in bytes, and is used as the sort column for the output. That is, the output from DBCC LOGINFO (if you only have single physical file) will be ordered by StartOffset. Notice that the first VLF always starts at offset 8192, which is the number of bytes in a page. The first page of the physical file is the file header, and does not store log records.

FSeqNo

The File Sequence Number indicates the order of usage of the VLFs. The row with the highest FSeqNo value is the VLF where current log records are being written. Notice that the FSeqNo values are not always consistent with the StartOffset, as VLFs are reusable after the log has been truncate. I'll discuss this value more in a future post, or you can get some info from Paul's article on how and when a VLF is reused. A value of 0 for the FSeqNo means that this VLF has never been used at all yet.

Status

Status has two possible values: 0 and 2. I'll be discussing these values in a lot more detail later. For now, the simple definition is that a value of 2 means the VLF is not reusable and a value of 0 means it is.

Parity

Parity has two possible values: 64 and 128. Every time a VLF is reused, the parity value is switched. Paul Randal discusses the parity value in more detail in this article.

CreateLSN

This value indicates when the VLF was created. If the CreateLSN is 0, it means the VLF was original equipment, created when the database was created. You can use this value to see how many VLFs were added each time the log grew. VLFs with the same CreateLSN value were created at the same time.

The whole point of describing all these columns was just to provide a lead-in to my little holiday gift to you. Here is a table that can be created in the master database, and then populated with the output of DBCC LOGINFO. The name of the table starts with sp_, which means you can use it from any database. Once you have captured all the rows from DBCC LOGINFO, you can query them using any TSQL queries.

Here the table creation script:

USE master
GO
IF EXISTS (SELECT 1 FROM sys.tables
WHERE name = 'sp_LOGINFO')
DROP TABLE sp_loginfo;
GO
CREATE TABLE sp_LOGINFO
(FileId tinyint,
FileSize bigint,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity tinyint,
CreateLSN numeric(25,0) );
GO

And here is how to populate it. I will use the AdventureWorks database for the example:

USE AdventureWorks;
GO

TRUNCATE TABLE sp_LOGINFO;
INSERT INTO sp_LOGINFO
EXEC ('DBCC LOGINFO');

You can select the VLFs from most recently used to the least:

SELECT * FROM sp_LOGINFO
ORDER BY FSeqNo DESC;

You can count how many VLFs have each status value:

SELECT status, COUNT(*) FROM sp_LOGINFO
GROUP BY status;

If you don't have a lot of VLFs, you can try to generate more by running SELECT INTO repeatedly. Make sure you are in FULL recovery to get the most log growth:

SELECT * INTO dbo.Orders FROM AdventureWorks.Sales.SalesOrderDetail;
DROP TABLE dbo.Orders;
GO 5

TRUNCATE TABLE sp_LOGINFO;
INSERT INTO sp_LOGINFO
EXEC ('DBCC LOGINFO');

SELECT status, COUNT(*) FROM sp_LOGINFO
GROUP BY status WITH ROLLUP;

You may be able to figure out other queries that provide useful or interesting information for you. I'll refer back to this table in my future discussions on DBCC LOGINFO and VLFs.

Enjoy!

~Kalen