Archive
Archive for November 21, 2012
Determining Estimated Completion Time from sys.dm_exec_requests
November 21, 2012
2 comments
According to Books Online, the following list of commands will generate a percent complete and an estimated completion time in the sys.dm_exec_requests DMO:
You can use this to determine approximately how long a backup, restore or DBCC command will run. This query will get you a nicely formatted time value.
USE master GO SELECT CASE WHEN estimated_completion_time < 36000000 THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2) + ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining], percent_complete, * FROM sys.dm_exec_requests WHERE percent_complete > 0
Categories: DMO, SQL Server, T-SQL, Uncategorized
DMO, SQL Server, T-SQL