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

2 Comments

  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