Get DML counts from Transaction Logs

[David_Fergen]

Instructions on how to get DML counts from Transaction logs:

1. --  Form the Online Log 
2. -- Make sure to replace the schema and table name.:

select Operation,count(*) from  

fn_dblog(null,null)  

where Operation in ('LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_DELETE_ROWS') 

and [PartitionId] = (select p.partition_id from sys.sysobjects s, sys.system_internals_partitions p 

where p.object_id = s.id and SCHEMA_NAME(uid) = N'DBO' and name = N'EMPLOYEE' 

) group by Operation; 

 

            3.-- Form a backup 

            4.-- Make sure to replace the schema, table name, and the name of the backup:

select Operation,count(*) from  

sys.fn_dump_dblog ('', NULL,NULL, 1, 

'D:\Temp\TLOGBackups\ReplicateSource\ReplicateSource_backup_2018_09_28_153559_0392869.trn', 

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) 

where Operation in ('LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_DELETE_ROWS') 

and [PartitionId] = (select p.partition_id from sys.sysobjects s, sys.system_internals_partitions p 

where p.object_id = s.id and SCHEMA_NAME(uid) = N'DBO' and name = N'EMPLOYEE' 

) group by Operation;