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)

block1

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 :

block2

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 🙂

Advertisements

2 Responses to “How to Locate Log Block in Log File”

  1. sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,truncate s Says:

    sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,trunc…

    […]How to Locate Log Block in Log File « Welcome to the Corner of Excellence[…]…

  2. log cabin holiday Says:

    log cabin holiday…

    […]How to Locate Log Block in Log File « Welcome to the Corner of Excellence[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: