Thursday, July 06, 2006

Using fn_dblog

I like the fn_dblog function even if it is undocumented. It enables you to read from you transaction log which contains very valuable information about stuff that is happening in your database. It is for example the only way I now at this time to determin which object experienced a page split if you search for the LOP_DELETE_SPLIT operation.

SELECT *
FROM ::fn_dblog(NULL, NULL)
WHERE operation = 'LOP_DELETE_SPLIT'

This is the usage of that system function you see everywhere, just passing NULL's for the @start and @end parameters, which could limit the LSN's (Log Sequence Number) to be searched. It took me some time to figure it out because just passing the output of the function as a parameter simply doesn't work. You can't use a LSN like for example 00000073:000020fa:0001 as a value for the parameters, you have to translate the hexadecimal values into its numeric equivalent (115:8442:1).

Let's show this with some code and because there's no easy way to make this conversion, I came up with something quick & dirty. At last an example of the actual usage of the parameters of fn_dblog on the web ;-)

SET NOCOUNT ON
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)

SET @LSN = (SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))
PRINT @LSN

SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT @tbl EXEC(@stmt)

SET @LSN_HEX =
(SELECT i FROM @tbl WHERE id = 1) + ':' + (SELECT i FROM @tbl WHERE id = 2) + ':' + (SELECT i FROM @tbl WHERE id = 3)
PRINT @LSN_HEX

SELECT *
FROM ::fn_dblog(@LSN_HEX, NULL)

8 comments:

deepvoids said...

Thanx KillSpids's

mor deror said...

it would be great to see how to pass the second param as well, so that let's say one could view top 50 last transactions

Rama Charan said...

i was not able to understand how i can use this to read\interpret the transaction logs.

If you can explain it in detail it can help us better.

thanks

Unknown said...

Does it work for MS SQL Server 2008 R2?

Sergii Volchkov said...

It seems that now hexadecimal values can be passed directly to fn_dblog by just prefixing them with '0x':
SELECT * FROM ::fn_dblog('0x0000005b:0000002a:0003', NULL)

Andrew Jackson said...
This comment has been removed by the author.
Andrew Jackson said...

fn_dblog is an undocumented SQL Server function that reads the active portion of an online transaction log. Let's look at the steps you have to take and the way the results are presented. Run the fn_dblog function. Select * FROM sys. fn_dblog(NULL,NULL). for more about fn_dblog read this post http://sqlserveroverview.blogspot.in/2015/05/sql-log-forensics.html

Blogging About SQL Tips & Tricks said...
This comment has been removed by the author.