Systems Engineering and RDBMS

A useful undocumented function: fn_dblog

Posted by decipherinfosys on November 12, 2008

A friend of mine recently told me about this undocumented function: ::fn_dblog when I was talking to him about Lumigent’s Log Explorer product. He pointed me to this post by Andrew Novick which mentions about this function which has existed since quite some time. I have always been using DBCC SQLPERF(LogSpace) when I wanted to capture used and free space and at times DBCC LOGINFO with help from different sites out there to understand the output. I am a fan of Lumigent’s product and have used it quite a bit in our client engagements but the fact that something like this existed all along, was a welcome surprise to me so I thought that we would share this on the blog with our readers as well.

As the post from Andrew Novick points out, the syntax is :

::fn_dblog(@StartingLSN, @EndingLSN)

And if you provide Null as the value for both the parameters, then you will get a running record of everything from the start to the end (not a good idea in a production environment but something to play around with in your development/test environment to get an understanding of the output). The post from Andrew goes into some of the details of what the different columns are for.

Some other useful links for this undocumented function:

1) Identifying page splits using this function – over here.
2) Forum discussion @ Simple Talk – over here.

One Response to “A useful undocumented function: fn_dblog”

  1. Hi,

    Thanks for the thoughtful post, I too used to dig information from Log using dbcc sqlperf(logspace) and couple of week back I saw a post by Paul Randal about ::Fn_dblog where in he shows how to find who has droped the object…….really useful information

    Regards
    Hemantgiri S. Goswami

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: