With Microsoft SQL Server, sometimes there can be long running SQL Queries which you would like to get an estimated ETA from.

Tim Ford has wrote a brilliant article which will provide an ETA for the following types of queries:

• Database Backup
  • BACKUP
• Database Restore
  • RESTORE
• Index Reorganisation
  • DBREINDEX
• DBCC Operations, such as:
  • SHRINKFILE
  • SHRINKDATABASE
  • CHECKDB
  • CHECKTABLE
• Rollback Operations
  • KILLED/ROLLBACK

Running the following SQL query will give the following information for all transactions which are awaiting completion:

• Session ID
• Percentage Complete
• Time Elapsed in Seconds
• Wait Type
• Wait Time
• Last Wait Type
• Estimated Completion Time
• SQL Transaction Text
• SQL Statement Currently Executing

SELECT R.session_id, 
R.percent_complete, 
R.total_elapsed_time/1000 AS elapsed_secs, 
R.wait_type,
R.wait_time,
R.last_wait_type,
DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) estim_completion_time,
ST.text, 
SUBSTRING(ST.text, R.statement_start_offset / 2, 
 (
 CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
 ELSE R.statement_end_offset
 END - R.statement_start_offset 
 ) / 2
) AS statement_executing
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) ST
WHERE R.percent_complete > 0
AND R.session_id <> @@spid
OPTION(RECOMPILE);