Looking for Inserts Updates and Deletes in the Transaction Log
Published: 16th October 2014
This will form the basis of my next post but it needed covering separately in the first place… that being how you can see Inserts, Updates, and Deletes within the transaction log, and how you can tell which table they were performed against.

Surprisingly it’s not as hard to do this as you would think… but obviously you do need a transaction log to run this against and therefore it’s best done in FULL recovery, or potentially SIMPLE if you get in there fast enough before the log clears.

So what do you need to do? Well, first things first we need to look at the log.

To do this we use the fn_dbLog function, only returning the columns we’re interested in.

What I’m going to do is to clear the log (I’m using SIMPLE so it just requires a CHECKPOINT, not a backup) create a table, perform an insert, and then look in the log at what’s just happened…

use AdventureWorks2012
go

checkpoint
go

create table temp
(
  
id int
)
insert into temp
select
salesOrderID
from sales.SalesOrderHeader
go

select [Current LSN], [transaction ID] tranID, [end time] endTime, allocUnitName, operation
from ::fn_dbLog(null, null)
go


Scroll down in your result set and you’ll soon find something like this:

As you can see, there are all the inserts next to your table name… also you can see that they were performed within 1 single transaction as well.

Obviously this isn’t fool proof as we know that transactions can be rolled back… therefore to ensure this hasn’t happened we need to make sure there’s been a commit.

Scroll to the bottom of your results and you should see the COMMIT there…

Now we know it’s committed, all we have to do is count the number of LOP_INSERT_ROWS records and we know how many records were inserted into dbo.temp.

For updates the log returns LOP_MODIFY_ROW and for deletes we will see LOP_DELETE_ROWS.

Using this information we can see what happened with our tables… how many rows were inserted, updated, or deleted alongside the time when the respective transaction committed…

Note, with indexes you will see a different allocation object name, it will actually be the index itself and therefore we want to turn this into the parent table name. For this I’ll be using the allocation unit ID and then joining through several internal tables.

Also note that if you are updating a clustered index then SQL Server internally performs a delete and insert rather than an update. This can appear to skew results, but personally I’m interested in what’s happening in my server and therefore I’m fine with seeing the true operation rather than somehow merging the 2 commands into 1 update.

Anyway…

use AdventureWorks2012
go

if object_id('temp') is not null drop table temp
go

create table temp
(
  
id int primary key clustered,
  
id1 int
)

checkpoint
go

insert into temp
select top
2 salesOrderID, SalesOrderID
from sales.SalesOrderHeader
go

update temp
set
id1 = id1 + 10
go

delete from temp
go

select [Current LSN], [transaction ID] tranID, [end time] endTime, AllocUnitId, operation, Context
into #temp
from ::fn_dbLog(null, null)
where (operation in ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')
   and
context not in ('LCX_PFS', 'LCX_IAM'))
or
operation = 'LOP_COMMIT_XACT'
go

select object_name(p.object_id) objectName, t.[Current LSN], c.endTime, t.Operation
from #temp t
join sys.system_internals_allocation_units siau
on t.AllocUnitId = siau.allocation_unit_id
join sys.partitions p
on siau.container_id = p.partition_id
join
(
  
select tranID, endTime
  
from #temp
  
where Operation = 'LOP_COMMIT_XACT'
) c
on t.tranID = c.tranID
where t.Operation in ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')
go

if object_id('temp') is not null drop table temp
go

drop table #temp
go


Here you can see the 2 inserts, the 2 updates, and the 2 deletes.
Comments:
Daniel Adeniji
27/06/2017 02:16:00
Nice Insight.
NB: Comments will only appear once they have been moderated.