THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

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)



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.






Published Wednesday, November 5, 2008 2:12 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Armando Prato said:

Damn, I was just working on fn_dblog as a tip for the MSSQLTIPS web site.  Back to the drawing board.

November 6, 2008 9:22 AM

jchang said:

Darn, sorry I forgot, a belated happy b-day for kevin

November 7, 2008 9:27 AM

KKline said:

Thanks Joe! :^)

November 7, 2008 10:31 AM

Paul White: Page Free Space said:

From time to time, I encounter a system design that always issues an UPDATE against the database after a user has finished working with a record – without checking to see if any of the data was in fact altered. The prevailing wisdom seems to be that “the

February 26, 2011 8:19 PM

Leave a Comment


About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS,

This Blog



Privacy Statement