I spent a good portion of last weekend restoring databases from backup due to a large release. Nothing went wrong, luckily, but I was practicing in case something did. It goes without saying that I spent a ton of that time sitting in front of my PC wondering when the restores would finally finish. In my boredom I started looking at ways to use the percent_complete column in sys.dm_exec_requests. As I was working on my query I noticed a column named estimated_completion_time. I had never seen this column before, but since the server I was on was running SQL 2005 I can only guess it had been there for some time.
After doing a quick search on the internet for how to use the column I ended up in Books Online. The definition for the column published there is “Internal only. Is not nullable.” Now that I knew someone did not want me to use the field I really wanted to use it. The field contained a really big number so I started looking at how to translate it into a time. Given that it was a really big number and seemed to be counting down, I tried adding it as milliseconds to the current date. I was a little shocked when the query returned a time a few minutes into the future that ended up pretty close to right on the first try.
Here is the query that I came up with:
1 2 3 4 5 6 7 8 | SELECT command, session_id, percent_complete, DATEADD(ms, estimated_completion_time, GETDATE()) AS estimated_completion_time, GETDATE(), start_time |
As I ran the query throughout the weekend I noticed that it was not always 100% accurate. A big thing I noticed is when restoring a SQL 2005 database to a SQL 2008 server the percent complete will be at or near 100% and the estimated completion time will be the current time while the conversion from SQL 2005 to SQL 2008 happens. That said, I still find this query very useful for giving a ballpark end time to a multi-hour restore and also found it very helpful for gauging the impact of changes like shutting off other jobs on the server or asking people to postpone work that adds x hours to the process.
I hope you find this query useful. I expect that it will work for any process that reports progress so the applications are definitely not limited just to restores. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
UPDATE: Aaron Bertrand has a version of this query that shows more information available here: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/30/when-will-my-backup-restore-index-reorganize-finish.aspx
