Compartilhar via

Auditando Operações DML - Leitura do transaction log Com a Função sys.fn_dblog

  • Artigo

É possível rastrear operações DML (alterações de dados) no teu banco de dados baseando-se na leitura do transaction log.

Com o modo de recuperação FULL, esta tarefa é mais eficiente.

Primeiro, habilitamos a traceflag 2537 para considerar dados inativos (VLFs inativas) na leitura do transaction log.

Digamos que nós temos um banco de dados chamado test e queremos rastrear uma relação de tabela/quantidade de alterações baseando-se em um intervalo de horas (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

Importante observar que estas queries consideram todos os status de transações (exemplos: comitted e rolled back).