::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....