SQL Server - Get query completion – simple

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:

Results screen

Script results detailed description:

Column nameData typeDescription
percent_completerealPercentage 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_timedatetime Estimated time stamp when actual command will most probably finish.
textnvarchar(max)Text of the SQL query. Is NULL for encrypted objects.

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *