Dive Into SQL Server Transaction Log — Part 2

This is the second article in a series of articles about SQL Server transaction log file architecture. In this article, we will have a look at two SQL Server statements that allow examining transaction log details. If you would like to get an introductory information about how SQL Server transaction log works, read Dive Into SQL Server Transaction Log — Part 1

DBCC LOGINFO

The only way to be able to look at the structure of transaction log is to use the undocumented DBCC LOGINFO command. The syntax for the command is:

Where dbname and dbid are optional. If you do not specify dbname and dbid it will dump you the log contents for the current database. So, the result set that it gives back is one row for every VLF that is in the transaction log for that database, and the fields that it gives back are as follows:

  • RecoveryUnitId — added in SQL Server 2012 but currently unused
  • FileId — transaction log file ID within a database.
  • FileSize — VLF size in bytes.
  • StartOffset — starting offset of the VLF in the transaction log file, in bytes
  • FSeqNo — The VLF sequence number
  • Status — Whether the VLF is active or not (0 = inactive, 2 = active, 1 – is not used)
  • Parity — current parity bits (64 or 128, or 0 if the VLF hsa never been active)
  • CreateLSN — the LSN when the VLF was created (0 = the VLF was created when the transaction log file was initially created). All other VLFs that are added after the initial creation of the transaction log file will have non-zero CreateLSN.

We can execute the following command for the DBTest2014 database, that we have created in the first article:

And see the results:

DBCC_LOGINFO

 

DBCC SQLPERF (LOGSPACE)

The only way in Transact-SQL to examine the amount of the log used is DBCC SQLPERF. The syntax for the command is:

The command returns a result set with one row per database:

  • Database Name
  • Log Size (MB)
  • Log Space Used (%)
  • Status: always set to zero

In my environment the following command:

returns:
DBCC_SQLPERF

Also Read

Dive Into SQL Server Transaction Log — Part 3

Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.
Andrey Langovoy

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.