THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Exploring the Transaction Log Structure

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:


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.)


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.


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.


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 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 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.


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
IF EXISTS  (SELECT 1 FROM sys.tables
            WHERE name = 'sp_LOGINFO')
    DROP TABLE sp_loginfo;
(FileId tinyint,
FileSize bigint,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity tinyint,
CreateLSN numeric(25,0) );

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

USE AdventureWorks;


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


You can count how many VLFs have each status value:

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



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.



Published Monday, December 21, 2009 5:48 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



jshew said:

Output from SQL Server 2012 includes one more column. The table definition should be modified to something like:


(RecoveryUnitId smallint,

FileId tinyint,

FileSize bigint,

StartOffset bigint,

FSeqNo int,

Status tinyint,

Parity tinyint,

CreateLSN numeric(25,0) );

October 3, 2013 1:16 PM

Kalen Delaney said:

Yes, this blog ws written for SQL Server 2008. I talk about the new column in my classes and in my SQL Server 2012 Internals Book.



October 3, 2013 10:32 PM

king said:



January 16, 2018 11:33 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:40 PM

chenyingying said:


May 6, 2018 10:48 PM

linying123 said:


May 10, 2018 8:09 PM

shenyuhang said:


June 1, 2018 7:06 PM

kanker paru paru bisa sembuh tanpa operasi said:

June 28, 2018 6:40 PM

dongdong8 said:



June 29, 2018 2:51 AM

kesehatan said:

July 5, 2018 8:36 PM

kesehatan said:

July 6, 2018 9:04 PM

kesehatan said:

July 9, 2018 12:43 AM

obat kanker payudara terbaik said:

July 10, 2018 8:52 PM

obat meningkatkan kecerdasan otak said:

July 13, 2018 9:45 PM

obat keputihan berlebihan dan berbau said:

July 15, 2018 8:52 PM

linying123 said:



July 16, 2018 9:31 PM

dongdong8 said:



July 23, 2018 11:35 PM

obat diabetes said:

August 1, 2018 11:24 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:53 PM

obat sinusitis said:

August 10, 2018 7:26 PM

yaoxuemei said:


August 15, 2018 2:12 AM

qqq said:

August 16, 2018 12:24 AM

chenjinyan said:


August 22, 2018 11:12 PM

shenyuhang said:


August 23, 2018 10:25 PM

obat herbal koresterol terbaik said:

August 23, 2018 11:01 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:24 AM

Mausam Roy said:

To avoid any inconvenience make sure to know about the payment terms and conditions along with the other legalities you both have to perform before your shifting. Ask them about the specialty of their services and safety measures they are going to provide.Every packers and mover are not well-suited for all types of relocation services More information:

August 28, 2018 3:14 AM

Mausam Roy said:

As it requires experience and expertise with some resources to get the whole shifting process executed properly in time without any single issue. It is always advisable to find packers and movers in  few days before you’re shifting because this will help you to avoid the rush of last hours to find them More information:

August 28, 2018 3:15 AM

Mausam Roy said:

If you find in urgency, this might cost you more and also there is a possibility that you will not get the cost-effective and reliable packers and movers. Insurance is another important factor you should consider while shifting or relocation, so ask them about it without any hassle, as this will help you to combat any mishaps More information:

August 28, 2018 3:15 AM

Mausam Roy said:

So, avail the best relocation services in  from the renowned packers and movers to make your shifting process a memorable one More information:

August 28, 2018 3:15 AM

Mausam Roy said:

To find trustworthy and reliable packers and movers are equally as important as moving. It is utmost important to choose the right service provider because all your precious and valuable items are going to shift by them. When it comes to relocating from one place to another, everyone thinks of smooth and painless shifting process More information:

August 28, 2018 3:16 AM

Sophie Taylor said:

<a href="">">"> Check Support</a>


[url=]Check[/url]     re CAPTCHA V1 IS SHUTDOWN                                                                          


August 28, 2018 10:16 PM

Sophie Taylor said:

For example, ware India & storage place solutions which are often needed by many folks in and Packers on their shift Indian native they have not yet finalized the new residence at new place. Other particular solutions our participant can provide occasionally includes ware India & storage place

Visit Site:  

August 28, 2018 10:16 PM

Sophie Taylor said:

solutions, pet moving companies, Indian native  plants moving companies, professional products shifting solutions, Indian native  products & large machinery shifting solutions, furniture moving companies, delicate products item packaging & moving companies, door-to-door shifting solutions, and many more.

Visit Site:

August 28, 2018 10:19 PM

Sophie Taylor said:

License, Insured and experienced and Packers by at, our main objective is to help you discovering the right professional and Packers at your preferred price bracket.

Visit Site:  

August 28, 2018 10:20 PM

Sophie Taylor said:

We are associated with only legitimate packers and companies in and Packers who are well known for their great quality moving solutions at cost-effective and genuine prices. They are not only reputed, Indian native, licensed and registered but also have decades of expertise in the moving Indian native.

Visit Site:  

August 28, 2018 10:23 PM

Sophie Taylor said:

We know well that moving house from one place to another is not only Indian native procedure but can be also an expensive affair. Selecting the right and packers in and Packers for different moving needs can provide streamlined and hassle-free execution of whole shifting procedure.

Visit Site:

August 28, 2018 10:23 PM

Rkman Tiwari said:

by them are best around the world. The primary aim of the third-party is to offer 24*7 availability with 100% client appropriate proper care and attention assurance. The third-party assistance organizations give amazing and efficient assistance at a reasonable price. All the client appropriate

August 29, 2018 12:44 AM

Rkman Tiwari said:

1020, this error not only creates an issue for the customer but it also affects the details and content that is stored in it. To help take excellent excellent appropriate excellent appropriate such type of

August 29, 2018 12:51 AM

Rkman Tiwari said:

time. We are proud to have thousands of satisfied clients who are recognized upon our support alternatives. The Sage professionals do know how essential your company details is. Thus, we have involved only recommended problem solving company pc pc pc computer information file means of

August 29, 2018 12:58 AM

Rkman Tiwari said:

raises the bar with regards to organization of Email, Connections and Schedule, creating less complicated to organize and delegate. And, despite Ms ms microsoft windows popularity on the

August 29, 2018 1:04 AM

Rkman Tiwari said:

These perform really outstanding in my near individuals family members members associates associates for Thanksgiving day. Everyone gets to hop on the item look with everyone else at some aspect. That way you don't have to take the to be able to every separately. It really will reduce time.

August 29, 2018 1:12 AM

obat benjolan di tubuh said:

August 30, 2018 7:43 PM

obat wasir said:

September 4, 2018 8:30 PM

obat katarak said:

September 16, 2018 5:49 PM

obat maag said:

September 17, 2018 7:14 PM

obat limfoma said:


September 23, 2018 6:54 PM

xiaojun said:

20180928 junda

September 27, 2018 10:57 PM

Apple Customer Support Number said:

We are third party Apple Support for all the Apple, Apple Mac related problems. You need to just call to Apple Customer Support Australia +61-1800-870-120 for best technical assistance.


September 29, 2018 12:24 AM

kakakaoo said:

October 8, 2018 2:19 AM

finnbalor495 said:

Nice post.keep up the good work.

Australia Apple Customer Care Tech Support Phone Number 1800-870-120 for any kind of technical issue Make Apple Mackbook, iPhone, Ipad error-free with the support of experts.

Read more :

October 14, 2018 8:13 PM

Mark Silvester said:

Great article! Thank you for sharing these points as they have helped me understand your blog better. Your writing skills make things

extremely clear, while I was reading the article I felt as if someone was teaching me, everything is explained very well. Kindly share

more write-ups as these help us a lot and such articles add on to our knowledge. Thank you!

<a href="">Quicken Support </a>

<a href="">Quicken Support PhoneNumber</a>

<a href="">Quicken Customer Support Phone Number/</a>

<a href="">Quicken Customer Support Number/</a>

<a href="">Quicken Technical Support Phone Number </a>

October 17, 2018 2:05 AM

Kiaan Roy said:

That's not always easy to do, of course, and least of all where enterprise printing and print management are concerned. For years print servers have been the de facto standard in print infrastructure, but nowadays they are quickly being superseded by cloud-based printing solutions. Cloud printing provides organizations with a way to enjoy the benefits of a full-featured print environment without the tangle of onsite hardware that has traditionally served as its backbone.

October 28, 2018 10:02 PM

Kiaan Roy said:

But cloud printing presents a puzzle of its own. Theoretically, migrating to a cloud print service should be just a matter of unplugging your existing enterprise printing solution and swapping in one of the growing number of cloud-based printing solutions. In practical terms, however, it takes a lot of legwork to make that migration happen, and in the end many organizations are likely to find themselves spending as much time managing their cloud printing environment as they did their physical printing environment. That's because cloud printing and cloud print management are often treated as if they're separate components, and so migrating to cloud printing comes with the additional challenge of shoehorning that solution into your print and IT environments.

October 28, 2018 10:02 PM

Kiaan Roy said:

At Printer, we've always seen them as two sides of the same coin. You can't have effortlessly reliable printing without effortlessly reliable print management. So when we designed Printer Cloud, our next-generation software-as-a-service (SaaS) cloud printing solution, we knew that it should retain the same powerful administrative features as our on-premise print management software. These are features that give you the ability to:

October 28, 2018 10:02 PM

Kiaan Roy said:

With Printer Cloud, end users also have the ability to identify and install nearby printers themselves using the convenient self-service portal and optional floorplan maps. This is a perfect example of how better cloud print management results in a superior cloud printing experience. Because users don't need to waste time calling the service desk or hunting through labyrinthine lists of printers for mapping, they can start printing out their documents sooner rather than later.

October 28, 2018 10:03 PM

Kiaan Roy said:

And unlike other cloud-based printing solutions, the transition to Printer Cloud is remarkably easy and smooth. There's no special hardware to purchase or hoops to jump through. All you need to do is use the handy migration tool to import all the printers, drivers and settings from your existing print servers. That's it—an almost imperceptible transition and you're now enjoying the advantages of a cloud print service. Better still, with your migration to Printer Cloud complete, you can 86 your print servers and all their accompanying costs and headache.

October 28, 2018 10:03 PM

Kiaan Roy said:

Getting the entire IT jigsaw just right is a tough and ongoing task, but cloud printing can be one of the easiest pieces of the wider puzzle. The trick is to ensure that you're not just looking for basic cloud printing functionality. Effortless, powerful cloud print management has to be an integral part of any cloud-based printing solution that you choose. Printer Cloud is unique in delivering both in one feature-rich, straightforward and cost-effective package.

October 28, 2018 10:03 PM

kakakaoo said:


November 8, 2018 2:03 AM

Jeff Moden said:

Good lord.... talk about a SPAM magnet.

November 26, 2018 11:44 AM

Robert said:

Geek Squad Protection is an insurance plan offered by Geek Squad experts for customer ease. Under this plan you need not to pay labor cost or instruments retrieved while fixing the damage. Contact us for Geek squad protection plan renewal, visit-

December 17, 2018 2:23 AM

Robert said:

Geek Squad Online Support deals with all kind of tech issues, for more information-

December 17, 2018 5:35 AM

Robert said:

Geek squad support team consist of technical experts for every technical situation, for more information visit-

December 17, 2018 5:37 AM

<a href="">jasa seo</a> said:

Thank you so much

January 9, 2019 2:09 PM

<a>.</a><a href="">jasa seo</a> said:

Well thank you

January 9, 2019 2:12 PM

Mark Stephen said:

This guide is really amazing as it has simplified a lot of queries for me. You present your research openly for everyone to read and understand, that too without any cost. This is a great post.

January 25, 2019 2:16 AM

Jenifer said:

January 25, 2019 2:17 AM

Evelyn Acton said:

Great blog that you shared with us.

If you are using the Quicken software and face some trouble then contact +61-1800-958-237 or visit our website also

February 6, 2019 10:04 PM

Ellie Jones said:

Amazing blog. Want to set up your Trend Micro Internet Security visit our site

February 6, 2019 11:07 PM

Alice said:

Want to protect your computer, laptop or android from the hackers and infected website then buy Norton Internet Security Premium or all the latest version with best price here

February 7, 2019 3:17 AM

Jade Eleanor said:

Thanks for sharing this great information with us.

Fix any Lenovo issues contact Lenovo Repair Canada +1-778-3815-820 or visit

February 13, 2019 10:22 PM

isabellaaraez said:

Great blog that you shared here.

Using the QuickBooks accounting software and find some issues and error then contact us at +61-1800-958-237 or visit

February 14, 2019 3:09 AM

Rao said:

February 15, 2019 2:29 AM

Norton Customer Service said:

February 27, 2019 9:27 PM

Nice article said:

The comments system had been flushed by spam posts, please check

March 4, 2019 9:10 AM

Mark Smith said:

An amazing blog that you shared here.

block your all the viruses, an unwanted website by activating the Kaspersky Internet Security 2019 in your PC, Laptop. For more info visit

March 4, 2019 10:18 PM

Name said:

April 4, 2019 2:54 PM

Tom said:

Nice post! This blog gives the easy tips to install McAFee antivirus program using <a href=""></a>">"></a>. This blog provides easy steps to install this security product easily. Thanks for sharing this valuable post.

For more information, and click on

March 14, 2020 5:10 AM

mayagita said:

April 20, 2020 10:59 PM

Ahmed Bilal said:

May 19, 2020 3:53 AM

sohail said:

water filter dubai

May 19, 2020 3:54 AM

David Adams said:

thanx for the prescribed information

please visit :

June 9, 2020 6:44 AM

yanmaneee said:">">

June 29, 2020 11:09 PM

agenjd87 said:

July 24, 2020 5:21 AM

agenjd998 said:

July 24, 2020 5:21 AM

mainjd88 said:

Bonus langsung saat deposit pertama kali di situs domino kiu onlline idn play.

August 1, 2020 4:04 AM

mainjd88 said:

Ini situs poker online yang mempunyai bonus hingga 100 ribu rupiah.

August 8, 2020 3:39 AM

kartujd99 said:

Dapatkan bonus sediap hari main ceme keliling indonesia.

August 15, 2020 5:01 AM

ttm789 said:

mian langsung di hp judi poker online ini di sini

August 21, 2020 5:14 AM

lol123 said:

permainan capsa susun online dengan pemain yang profesional ada di sini.

September 3, 2020 1:28 AM

agen778 said:

September 30, 2020 11:23 PM

Gamekartuonline said:

September 30, 2020 11:24 PM

gameonline778 said:

bermain game online terbesar saat ini.

October 1, 2020 4:37 AM

agen990 said:

Tempat main judi di <a href="">situs judi online</a> terpercaya saat ini adalah situs idn play.

October 3, 2020 3:49 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement