How to Locate Log Block in Log File

In this topic, I will discuss about how to locate the physical offset of certain log block in Microsoft SQL Server 2000’s log file. The Log file is actually arranged in log block, and each log block contains log records, depends on the number of slots that is used.

For example, I already created the test database. Using the DBCC LOGINFO we get :

DBCC loginfo(test)

From here, we can deduce that the current active log file is located as offset 8192 or 0x2000 in the log file.

To determine which offset from log file for each log block, we have to get the LSN of particular log record of interest. For example, to determine the offset location of checkpoint data, we have to get the LSN information of checkpoint :

To Get checkpoint data :

DBCC TRACEON(3604)
dbcc dbinfo(test)
dbi_checkptLSN
--------------
m_fSeqNo = 6              m_blockOffset = 78        m_slotId = 1

To calculate the offset location of checkpoint LSN log block, we can use the formula :

Physical Offset of Log Block = startOffset of Particular File Sequence + (m_blockOffset * 512)

So, in this case, the physical offset is located at :

8192 + 78 * 512 = 48128 or at offset 0xBC00 in physical log file :

If this sample LSN (checkpoint LSN) is translated into LSN format, then we have : 00000006:0000004e:0001. 0x6 is file sequence (m_fSeqNo), 78 = 0x4E is m_blockOffset, and 0x1 is m_slotId.

Using the above format, we can then translate any LSN of interest to the physical offset location in the log file for a low level view of log block.

You can see my next post about the structure of log block. Stay tuned 🙂