Retrieve information about query completion percent and estimated finish time. This is useful when running big databases restore or some hard DBCC database command. You want to check actual state or plan your activities based on estimated finish time.
Just put session id for process you want to get completion info about and run the script. Keep in mind that this is only estimation and real finish time can vary based on actual server load.
Warning: It is not showing all types of commands. Supported commands listed below:
- 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
SELECT r.percent_complete, estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP), t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.session_id = [session id here]
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| percent_complete | real | Percentage of work completed for the following commands: 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. Is not nullable. |
| estimated_finish_time | datetime | Estimated time stamp when actual command will most probably finish. |
| text | nvarchar(max) | Text of the SQL query. Is NULL for encrypted objects. |