::fn_dblog

I've tried more to know the details of transaction log column information.  During this time, I found a system function ::fn_dblog which is more convenient than DBCC LOG for the following reasons even though it does not have any documentation:

1. DBCC LOG consumes more power to retrieve transaction log info.

2. It is a DML query and flexible than DBCC.

Syntax: SELECT * FROM ::fn_dblog(<START_LSN>, <END_LSN>) [WHERE ...]

START_LSN :  Starting Log Sequence Number

END_LSN: Ending Log Sequence Number

We know that the transaction log keeps records by  CURRENT LSN column.  Every transaction entry can be identified by an unique LSN.  The fn_dblog function fetches all the records from log.  Here you can pass start & end LSN to limit your search, otherwise you should NULL on both.

Example:

Use MyTestDB
SELECT * FROM ::fn_dblog(NULL, NULL)

Following column are displayed:

First section:

Current LSN                         Operation                       Context                     Transaction ID
-----------------------------------------------------------------------------------------------------------------
0000003a:00000077:0001 LOP_SET_BITS                LCX_DIFF_MAP          0000:00000000
0000003a:00000077:0002 LOP_BEGIN_XACT           LCX_NULL                  0000:00000c1b
0000003a:00000077:0003 LOP_MODIFY_COLUMNS LCX_CLUSTERED        0000:00000c1b
0000003a:00000077:0004 LOP_SET_BITS                LCX_DIFF_MAP           0000:00000000

Second Section:

Previous LSN                       FlagBits Alloc                       Alloc                         Page ID
                                                         UnitID                    UnitName
-------------------------------------------------------------------------------------------------------------------
00000000:00000000:0000 0x0000 6488064                 Unknown Alloc Unit 0001:00000006
00000000:00000000:0000 0x0200 NULL                       NULL                       NULL
0000003a:00000077:0002 0x0200 281474981691392 sys.sysdbfiles.clst   0001:00000055
00000000:00000000:0000 0x0000 6488064                 Unknown Alloc Unit 0001:00000006

and more...... 

In this, I can understand the purpose of the following column based on the semantics:

Operation -

Which type of operation occurred.

Some meaning values are: LOP_BEGIN_XACT - Internal checkpoint of an INSERT or UPDATE transaction.

LOP_MODIFY_COLUMNS - update statement called and LOP_INSERT_ROWS - insert row.

Alloc Unit Name  - object name

Okay come back to the function.

In this function, you cannot pass the LSN value as displayed above (0000003a:00000077:0001).  The function accepts only number formated NVARCHAR values.  So, we have to convert these hexa-decimal to NVARCHAR.

How........?

You can do it in two ways:

I. Using your calc (Calculator in WinXP)

   1. Let the hexa 0000003a:00000077:0001.  It contains 3 parts separated by ':'.

   2. Open you calc in hex mode.

   3. Cut the first part of hexa and paste it into calc.

   4. Change the mode to decimal.

   5. Copy the displayed value to a notepad.

  6. Do the step 3 - 5 for other 2 parts.

  7. Finally, you will get a string '58:119:1'.

Instead of above fn_dblog call, use the below:

Use MyTestDB
SELECT * FROM ::fn_dblog('58:119:1', '58:119:4')

It displays only LSN from 0000003a:00000077:0001 to 0000003a:00000077:0004.

II. Programmatically (Thanks killspid: http://killspid.blogspot.com/)

Here is the excerpt from his blog:

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)

Okay, but how can i know the details of other columns?

Its a series of PAIN....