Certain statements report the percent complete in
Percentage of work completed for the
- ALTER INDEX REORGANIZE
- AUTO_SHRINK option with ALTER DATABASE
- BACKUP DATABASE
- DBCC CHECKDB
- DBCC CHECKFILEGROUP
- DBCC CHECKTABLE
- DBCC INDEXDEFRAG
- DBCC SHRINKDATABASE
- DBCC SHRINKFILE
- RESTORE DATABASE,
- TDE ENCRYPTION
For other statement, you'll have to wait it out and see.
First of all you'd have to check whether the 'query' is making any progress or is just sitting idle, blocked b something else. The same DMV mentioned above will immediately reveal this, the
wait_resource are a dead give away: if they change, its making progress, if they stay fixed (and
wait_time is growing) the query is stuck and the
blocking _session_id is the blocker.
If the query was indeed making progress, and is one single query that is moving the rows, I gotta say: one does not go around and blindly moves 10M rows. If you did this in one single transaction, then your transaction log has been growing over the past 10 hours continuously. Every log growth event is a complete freeze of the database as it waits for the log to expand to zero out the new allocated disk space. Then the activity will resume, only to hit a new growth event in a short bit. By now, your database log must had grown several hundred times likely. This happens under all recovery models, including SIMPLE.
You could abort the query, but you'd have to wait until the 10 hours of the transaction will roll back. A 10h transaction can take another 10h to roll back, or more, or less, it depends on many many factors.
You could shutdown the server in panic and restart it, only to face the recovery of the database that is trying to undo the 10h transaction. Recovery will last, guaranteed, longer than rolling back the transaction.
So this is why one does never move 10M rows in a single 'query'. Batching the move in smaller sets is a must. Of course, this requires a carefully written program to do it.
Some users are lucky, they learn by stumbling on posts like this and making a note to self 'don't move 10M rows in one single operation'. Other learn the hard way... but they (hopefully) know better next time.