tim laqua dot com Thoughts and Code from Tim Laqua

25Sep/092

Determining when RESTORE DATABASE command will complete (SQL Server 2008)

Ah, I see you just started restoring that 1TB monster and now everyone wants to know when it's going to be finished, where you're at in the process, etc. Fear not, Microsoft is very good at making up fictional numbers for us to use as rough estimates! I usually add 10-20% on top of these estimates just incase the database gremlins wander by to ruin your day again. Or incase you encounter "storage issues."

select 
	 percent_complete AS [PercentComplete]
	,estimated_completion_time/1000.0/60.0 AS [RemainingMinutes]
	,total_elapsed_time/1000.0/60.0 AS [ElapsedMinutes]
	,(estimated_completion_time+total_elapsed_time)/1000.0/60.0 AS [TotalMinutes]
	,DATEADD(MILLISECOND, estimated_completion_time, GETDATE()) AS [EstimatedTimeOfCompletion]
	,st.text AS [CommandSQL]
from sys.dm_exec_requests r
	cross apply sys.dm_exec_sql_text(r.sql_handle) st
WHERE command LIKE '%RESTORE DATABASE%'
Comments (2) Trackbacks (0)
  1. “Storage Issues” defined as running out of space on a drive. Nothing actually to do with the SAN functionality.

    • you mean like the SAN functionality where it like… provides enough storage to serve critical business functions? no, that’s not part of SAN functionality… 😉


Leave a comment

No trackbacks yet.