SQL Server – fn_dblog Not Showing Records While LDF File is Large

sql servertransaction-log

I need to find some specific transactions in the transactionlog file, so I use fn_dblog function. But the function is only returning a few rows. Meanwhile the LDF file for my database is a couple of gigabytes at least.

I know the fn_dblog function is only searching the active part of the log:

  • But what does that mean?

  • When does a part of the transactionlog becomes inactive?

  • And how can I search the inactive part?

Best Answer

Reference:

SQL Server Transaction Log Architecture and Management Guide

I know the fn_dblog function is only searching the active part of the log: But what does that mean?

Active Log

The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log. This is the section of the log required to do a full recovery of the database. No part of the active log can ever be truncated. All log records must be truncated from the parts of the log before the MinLSN.

The following illustration shows a simplified version of the end-of-a-transaction log with two active transactions. Checkpoint records have been compacted to a single record.

enter image description here

LSN 148 is the last record in the transaction log. At the time that the recorded checkpoint at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. This makes LSN 142, the Begin transaction record for Tran 2, the MinLSN.

When does a part of the transactionlog becomes inactive?

In the above example as soon as Checkpoint (LSN147) happens LSN141 portion becomes inactive. SQL server manages these in chunks of Virtual Log File (called VLF).

What the VLF by Mike Hillwig

In general, Log truncation occurs automatically after the following events, except when delayed for some reason:

  • Under the simple recovery model, after a checkpoint.
  • Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.

And how can I search the inactive part?

I am not aware of any documented Microsoft process of doing that. Paul Randal shows an undocumented process called fn_dump_dblog in this article.

There are 3rd party products you can use.

One of those here:

https://www.apexsql.com/sql_tools_log.aspx

Related Question