Systems Engineering and RDBMS

Archive for November 12th, 2008

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.

Posted in SQL Server | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers