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

Search results matching tags 'Internals', 'transaction log', and 'metadata'

Geek City: Exploring the Transaction Log Structure