SQL Server Execution Completion Estimation
A simple T-SQL command that checks the progress of a particular SQL request, such as the progress of DBCC SHRINKDATABASE.
It is very frustrating trying to figure-out when an adhoc maintenance operation, such as a Database Recovery, or a SHRINKDATABASE command will finish.
This is a big issue when it comes down to a database on a production environment, for example, today we noticed that one of our drives is running very low on space, this was attributed to the creation of 2 indexes on a FileGroup located on the drive in question, I have migrated the indexes on a more suitable FileGroup, but the space has not been reclaimed by SQL Server, now generally we would wait for the backup to take place and truncate the database, and that is definitely the smarter and the recommended option (see post on Shrinking SQL Server Databases, but in this case we decided to run a SHRINKDATABASE command instead.
The shrink database command was taking a while to complete, this started to become a real issue when the time for the daily maintenance operation was due to kick in, and so in order to get an estimation on how long the request has left, you can execute the DM_EXEC_REQUESTS SQL DMV, as so:
percent_complete,
start_time,
STATUS,
command,
estimated_completion_time,
cpu_time,
total_elapsed_time,
*
FROM sys.dm_exec_requests
--WHERE command = 'DbccFilesCompact'
This should give you two metrics that help you realize when is the query likely to finish:
- percent_complete: This is the percentage of the request that has been completed.
- estimated_completion_time: This is very much a rough estimation of how many milliseconds it will take to complete the request. This is actually for internal SQL Engine usage only as it is most likely wrong all the time, but the longer you look at this DMV, the more you’ll start to get a feel for how this translates to actual time.
Remember it is very difficult for an estimation to be accurate, as it depends on many SQL Server specific and server-wide resource utilisation throughout the lifetime of the request you are trying to monitor. This is even more difficult when you have a multi-purpose server box.
Check out MSDN for more details on dm_exec_requrests DMV.
Hope this helps you! my query has been stuck on 92% for hours now 
Leave a Reply
Want to join the discussion?Feel free to contribute!