THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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)

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

 

Published Wednesday, November 05, 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

Comments

 

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

(required) 
(required) 
Submit

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, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement