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

Geek City: Fragmentation on the System Tables

Kalen Delaney — Fri, 18 Jan 2008 01:05:00 GMT

It's my first post of the new year. I hope it's starting out well for all of you! New year, but sometimes the same old questions. I got another email asking about defragging the system tables. It seems to be in the Hit Parade of FAQs.

First of all, WHY do you think you would need to defrag a system table? Fragmentation is only a problem when you are performing an in-order scan on a table of more than 100 pages or so, and how often do you do that to a system table that is that big? Rarely, I would assume, but let me know if you have seen an actual need for defragging a system table.

In SQL Server 2000, you can actually use a system table as a parameter to DBCC SHOWCONTIG to see the fragmentation values. One of my readers reported that he tried running a DBCC INDEXDEFRAG on SQL 2000 system tables and while sometimes it worked, sometimes it corrupted the table! It doesn't sound worth it to me.

And now, with SQL Server 2005 there is no way to see the fragmentation on the system tables. What about using the new Dynamic Management Object sys.dm_db_index_physical_stats? It requires an object_id as a parameter, and if you select from the metadata view sys.all_objects you can see the object_id for the real system tables, even if you're not using the DAC. Try this:

select * from sys.all_objects
where type = 'S'

You should see all the system tables in your current database, and their object IDs. So you could try passing the id to the management view. Here I'll try it for syshobts, which has an ID of 15:

select * from
sys.dm_db_index_physical_stats(1, 15, null, null, 'detailed')

No results are returned. If I replace the 15 with null, to indicate I want all objects, I do not see any objects with IDs less than 100, which means there are no system objects reported.

I did notice one interesting behavior while playing around with this concept. If I use the old DBCC SHOWCONTIG and try to get a report for a system table, this command gives me an error:

dbcc showcontig ('syshobts')

Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "syshobts". Check the system catalog.

However, if I qualify the table name with the schema name, something different happens.

dbcc showcontig ('sys.syshobts')

This time, I don't get an error. I don't get a fragmentation report, but I don't get an error. I just get the message that DBCC execution completed. So it's obvious that there is a difference here. If I then intentionally misspell the object name

dbcc showcontig ('sys.syshobbits')

now I get the error that SQL Server cannot find the object. So it seems that in SQL Server 2005, DBCC SHOWCONTIG is deliberately filtering out the real system tables, and it was by design to not return fragmentation information. So even if you could run defrag on your system tables, you could never know what impact you had!

I think there's better ways to spend our time.

Have fun!

~Kalen

Search results matching tags 'Internals' and 'metadata'

Geek City: Exploring the Transaction Log Structure

Geek City: Fragmentation on the System Tables