Auditing DML Operations Reading Transaction Log With sys.fn_dblog

It's possible to track DML operations (data changes) on your database based on reading transaction log.

With recovery model as full this task can be work more efficiently.

First we enable traceflag 2537 to consider reading transaction log inactive data (inactive VLFs).

Say we have a database named test and need to track a count about Table/DMLs based on a datetime range (2018-03-02 06:00 to 2018-03-02 07:00).

USE [test]
GO
 
DBCC TRACEON(2537)
GO
 
-------DELETE tracking--------
SELECT DISTINCT  AllocUnitName AS  [Table], COUNT(Operation) AS  DELETE_rows_Count 
    FROM sys.fn_dblog(NULL, NULL)
        WHERE Operation = 'LOP_DELETE_ROWS' AND AllocUnitName  NOT LIKE  'sys%'
            AND AllocUnitName <> 'Unknown Alloc Unit' AND Context  IN ('LCX_HEAP', 'LCX_CLUSTERED') 
            AND [Transaction ID] IN  (SELECT  [Transaction  ID] FROM  sys.fn_dblog(NULL, NULL)  
                                        WHERE [Begin Time] BETWEEN '2018/03/02 06:00' AND '2018/03/02 07:00') 
GROUP BY  AllocUnitName ORDER  BY DELETE_rows_Count DESC
GO
 
-------INSERT tracking--------
SELECT DISTINCT  AllocUnitName AS  [Table], COUNT(Operation) AS  INSERT_rows_Count 
    FROM sys.fn_dblog(NULL, NULL)
        WHERE Operation = 'LOP_INSERT_ROWS' AND AllocUnitName  NOT LIKE  'sys%'
            AND AllocUnitName <> 'Unknown Alloc Unit' AND Context  IN ('LCX_HEAP', 'LCX_CLUSTERED') 
            AND [Transaction ID] IN  (SELECT  [Transaction  ID] FROM  sys.fn_dblog(NULL, NULL)  
                                        WHERE [Begin Time] BETWEEN '2018/03/02 06:00' AND '2018/03/02 07:00') 
GROUP BY  AllocUnitName ORDER  BY INSERT_rows_Count DESC
GO
 
-------UPDATE tracking--------
SELECT DISTINCT  AllocUnitName AS  [Table], COUNT(Operation) AS  UPDATE_rows_Count 
    FROM sys.fn_dblog(NULL, NULL)
        WHERE Operation = 'LOP_MODIFY_ROW' AND AllocUnitName  NOT LIKE  'sys%'
            AND AllocUnitName <> 'Unknown Alloc Unit' AND Context  IN ('LCX_HEAP', 'LCX_CLUSTERED')
            AND [Transaction ID] IN  (SELECT  [Transaction  ID] FROM  sys.fn_dblog(NULL, NULL)  
                                        WHERE [Begin Time] BETWEEN '2018/03/02 06:00' AND '2018/03/02 07:00') 
GROUP BY  AllocUnitName ORDER  BY UPDATE_rows_Count DESC
GO

Important: these queries tracks all transaction states (commited, rolled back, and so on).