Wednesday, November 05, 2008

Better Ways to Get Transaction Log Information

by KKline via Kevin Kline on 11/5/2008 7:12:00 PM

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 http://www.novicksoftware.com/udfOfWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm, or google '::fn_dblog' for lots of other sources of information about it.

Thanks,

-Kevin