Skip navigation

Better Ways to Get Transaction Log Information

If you've been around a while, you tend to do things the way you first learned how to do them. This can turn you into an "old timer" whose oblivious to new and better ways to do things that have appeared in the newer releases of the technology. Take measuring log space, for example. If I wanted to find out how much log space has been utilized, I would dash off a DBCC SQLPERF(LOGSPACE) statement. But SQLPERF(LOGSPACE) only shows used/free space and not much more. I might use DBCC LOGINFO to see how the active log "moved across the ldf file", then examine the values columns such as "active" for more understanding. Or I could go even more old-school, and look at the PerfMon counters or perhaps at the PerfMon counters exposed through the system table sysperfinfo. However, I want to thank Tibor Karaszi and Gert Drapers for this great tip. Simply use this query: SELECT SUM(\[Log Record Length\]) FROM ::fn_dblog(null, null) WHERE... The ::fn_dblog pseudotable provides a wonderful amount of information about the transaction log. Read more about this undocumented function at, or google '::fn_dblog' for lots of other sources of information about it. Thanks, -Kevi

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.