Tuesday, November 4, 2014

Estimated Completion Time of SQL Queries (BACKUP/RESTORE)

When you restore or backup a huge database, you might be anxious to know the estimated completion time of that command.

There is a "STATS" parameter that you can use with the command, but it is not dynamic.

The following query can help you in such scenario.

select dr.session_id,      
       dr.command,
       percent_complete,
       dr.start_time,
       dateadd(mi,estimated_completion_time/60000,getdate()) EstimatedCompletionTime,
       ds.text
from  sys.dm_exec_requests dr cross apply sys.dm_exec_sql_text(dr.sql_handle) as ds

where estimated_completion_time > 0

Note that, only following SQL Commands have the percentage value populated. 

·         ALTER INDEX REORGANIZE
·         SHRINK
·         BACKUP DATABASE
·         DBCC COMMANDS
·         RECOVERY
·         RESTORE DATABASE,
·         ROLLBACK
·         TDE ENCRYPTION