DBMS Guide: Find Which Column in Which Table Updated from logfile

Sometimes, database developers need to work with a database in which they don't have any permission to add any procedure or trigger, because that database is for someone else but they need change tracking. In this tip, I will show a solution for this problem.

Background

First of all, I want to appreciate Muhammad Imran for the great information on his site:http://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1.
Then, as a brief history and description: In SQLServer, the log file (LDF) is a Microsoft solution for implementing transactions, and rollback action uses the log file for rolling back, so we can use the LDF file to track data.

Using the Code

SQL Server has undocumented T-function sys.fn_dblog that is used for reading log file and procedure DBCC PAGE that is used for details of data page like allocation_UnitId, Fields, Values, SlotId.

Collapse | Copy Code

Create PROCEDURE Find_UpdatedColumn_In_Table
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(MAX),
@Date_From datetime='1900/01/01',
@Date_To datetime ='9999/12/31',
@FeildName nvarchar(max)
AS
begin
DECLARE @parms nvarchar(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT

DECLARE @ConsolidatedPageID VARCHAR(MAX)
Declare @AllocUnitID as bigint
Declare @TransactionID as VARCHAR(MAX)
Declare @Operation as VARCHAR(MAX)
Declare @DatabaseCollation VARCHAR(MAX)

/*  Pick The actual data
*/
declare @temppagedata table 
(
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)

declare @pagedata table 
(
[Page ID] sysname,
[AllocUnitId] bigint,
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)

    DECLARE Page_Data_Cursor CURSOR FOR 
    /*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log 
    for modified records & Get its Slot No, Page ID & AllocUnit ID*/
    SELECT [PAGE ID],[Slot ID],[AllocUnitId]
    FROM    sys.fn_dblog(NULL, NULL)  
    WHERE    
    AllocUnitId IN 
    (Select [Allocation_unit_id] from sys.allocation_units allocunits
    INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)  
    AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
    AND partitions.partition_id = allocunits.container_id)  
    Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
    AND Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')  _
    AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
    /*Use this subquery to filter the date*/

    AND [TRANSACTION ID] IN (SELECT DISTINCT _
    [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
    WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
    --AND [Transaction Name]='UPDATE'
    AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

    /****************************************/

    GROUP BY [PAGE ID],[Slot ID],[AllocUnitId]
    ORDER BY [Slot ID]    
 
    OPEN Page_Data_Cursor

    FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @hex_pageid AS VARCHAR(Max)
        /*Page ID contains File Number and page number It looks like 0001:00000130.
          In this example 0001 is file Number &  
          00000130 is Page Number & These numbers are in Hex format*/
        SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX_
        (':',@ConsolidatedPageID)) -- Separate File ID from Page ID
        SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX_
        (':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))  ---Separate the page ID
       
        SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary_
        (substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', _
        'varbinary(max)')) -- Convert Page ID from hex to integer
        FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos) 
                    
        DELETE @temppagedata
        -- Now we need to get the actual data (After modification) from the page
        INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + _
        @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;'); 
        -- Add Page Number and allocUnit ID in data to identity which one page it belongs to.                    
        INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,_
        [ParentObject],[Object],[Field] ,[Value] FROM @temppagedata

        FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID
    END

CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor
DECLARE @Newhexstring VARCHAR(MAX);

--select * from  @PageData
--select * from  @temppagedata
DECLARE @ModifiedRawData TABLE
(
  [ID] INT IDENTITY(1,1),
  [PAGE ID] VARCHAR(MAX),
  [Slot ID] INT,
  [AllocUnitId] BIGINT,
  [RowLog Contents 0_var] VARCHAR(MAX),
  [RowLog Contents 0] VARBINARY(8000)
)
--The modified data is in multiple rows in the page, 
--so we need to convert it into one row as a single hex value.
--This hex value is in string format
INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId]
,[RowLog Contents 0_var])
SELECT B.[PAGE ID],A.[Slot ID],A.[AllocUnitId]
,
(
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([VALUE],CHARINDEX(':',[Value])+1,48),'†','')
FROM @pagedata C  
WHERE B.[Page ID]= C.[Page ID] And A.[Slot ID] =LTRIM(RTRIM_
(SUBSTRING(C.[ParentObject],5,3))) And [Object] Like '%Memory Dump%' 
Group By [Value] FOR XML PATH('') ),1,1,'') ,' ','')
) AS [Value]

FROM sys.fn_dblog(NULL, NULL) A
INNER JOIN @pagedata B On A.[PAGE ID]=B.[PAGE ID]
AND A.[AllocUnitId]=B.[AllocUnitId]
AND A.[Slot ID] =LTRIM(RTRIM(Substring(B.[ParentObject],5,3)))
AND B.[Object] Like '%Memory Dump%'
WHERE A.AllocUnitId IN 
(Select [Allocation_unit_id] from sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 
AND partitions.partition_id = allocunits.container_id)  
Where object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation in ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS') _
AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*Use this subquery to filter the date*/

AND [TRANSACTION ID] IN (Select DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) 
Where Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')  
AND [Transaction Name]='UPDATE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/
GROUP BY B.[PAGE ID],A.[Slot ID],A.[AllocUnitId]--,[Transaction ID]
ORDER BY [Slot ID]

-- Convert the hex value data in string, convert it into Hex value as well. 
UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value_
('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
FROM @ModifiedRawData

-- 
select Field, Value  from @temppagedata t
inner join @ModifiedRawData m on LTRIM(RTRIM(SUBSTRING_
(t.[ParentObject],5,3)))  = cast(m.[Slot ID] as nvarchar(1000))
where t.Field =  @FeildName

end

EXEC Find_UpdatedColumn_In_Table  'Test','dbo.Test','2000/01/01','9999/12/31',N'id'