fn_dblog not showing records while LDF file is large 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?
the overall issue with your endeavor, while I commend it, is that getting the log isn't the hard part. Reading the log is, as it isn't documented at all. There are 3rd party products that can help you, but you reading the log and asking these questions isn't going to work out very well. This wasn't written to put you off, merely to express that the log isn't very human readable, documented, or otherwise made for people to go slogging through it.

With that, the below should help you:


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


Unless you set the proper trace flag. I'm not going to reinvent the wheel, here, Paul already has a great post on this.


  •But what does that mean?


It means without the TF it won't look at inactive parts of the log... inactive VLFs.


  •When does a part of the transactionlog becomes inactive?


When it's not longer needed by recovery (and/or, depending on recovery model) when it's been successfully backed up. See the log architecture link, above.


  •And how can I search the inactive part?


See Paul's blog.