I’m pretty sure that we’ve all found ourselves in the situation where we’ve run an INSERT, UPDATE or DELETE script and it seems to be taking an age to run.

We’ve done all the usual checks, there’s no blocking going on, things are happening but it just seems to be taking an age to finish.  What do we do?  Do we rollback? How much longer is it likely to take?

If only there was a way for us to know exactly how many rows our script had affected so far.

If you’ve been in this situation, I’m pretty sure that at some point you will have taken a look at sys.dm_exec_requests after seeing that it has a ‘percent_complete’ column.

Your excitement is quickly quashed when you realise that this column is showing 0% complete for your insert that’s been running for the past three hours.

A quick check of the documentation will tell your that ‘percent_complete’ only actually works for the following statements

ALTER INDEX REORGANIZE

AUTO_SHRINK option with ALTER DATABASE

BACKUP DATABASE

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKTABLE

DBCC INDEXDEFRAG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

RECOVERY

RESTORE DATABASE

ROLLBACK

TDE ENCRYPTION

…not much good when we’re running an INSERT, UPDATE or DELETE.

So there’s no way that we can tell what the progress of our statement is?

Well, it would turn out that there is and to find out we need to turn to our trusty friend, the transaction log.

As we know, the transaction log will squirrel away an entry each time that a row is modified.  We can count up all the LOP_MODIFY_ROW, LOP_INSERT_ROW and LOP_DELETE_ROWS entries for our transaction and that will tell us just how many rows our transaction has altered so far.

The following script will dive in and return you the number of rows that have been modified, inserted or deleted by the SPID that you plug into @SPID

DECLARE @SPID INT = 54
SELECT COUNT(*)--fn_dblog.*
FROM fn_dblog(null,null)
WHERE
operation IN ('LOP_MODIFY_ROW', 'LOP_INSERT_ROWS','LOP_DELETE_ROWS') AND
context IN ('LCX_HEAP', 'LCX_CLUSTERED') AND
[Transaction ID] =
(SELECT fn_dblog.[Transaction ID]
FROM sys.dm_tran_session_transactions session_trans
JOIN fn_dblog(null,null) ON fn_dblog.[Xact ID] = session_trans.transaction_id
WHERE session_id = @SPID)

It’s worth noting that this script will return a the count of all rows that have been affected by the running transaction and not the statement.  If your transaction contains a number of statements, the count will be the total number of rows affected so far by all statements that have run and are running.