How Far Has My Update Got? Finding Out How Many Rows Your Long Running Insert, Update or Delete Has Actually Modified So Far

mile

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.

2 thoughts on “How Far Has My Update Got? Finding Out How Many Rows Your Long Running Insert, Update or Delete Has Actually Modified So Far

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: