How to tell the progress of a large SQL Server delete or update statement

If you have ever been in a situation where you are running a large delete (or update) on a table, and need to know the progress there is a way you can find out.
The way I do it is open a new window and run

Select 1 from <tablename> (NOLOCK) WHERE <your filter>

This will then give you all the rows in their uncommitted state, you can then predict fairly accurately how long a big delete is going to take, by comparing the row counts over a time period.

Then when your boss is breathing down your neck wanting to know how long the database is going to be down for you can say approximately 6 hours, instead of saying you have no idea!

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ


  1. Nice one… Didn’t know this, thanks. (Even if the post is rather old)

    Got an addition – just for the logs:

    select sum(1) from article with (nolock)

    Get’s the sum in a single row without output of X data rows.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.