SQL 2005 introduced some cool "data management views" (DMV’s) that let a DBA see what is happening on their database. Previously you had to use undocumented system tables and information schemas to get the info, and a lot of the info wasn’t even available. Well recently I was doing some backup/restore stuff on a database and wanted to know when it would finish. The first question I ask is – why isn’t this built into the SSMS view of a db, when it says "DatabaseName (Restoring)" why couldn’t they just add the % done at the end of something? Anyway’s – if you want to see what is restoring on your server and where it is at, go ahead and use this query:
percent_complete AS ‘PctComplete’,
start_time AS ‘StartTime’,
command AS ‘Command’,
b.name AS ‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%restore%’
AND estimated_completion_time > 0
I did notice that it says 100% complete but then the restore still takes about 20-30 seconds to finish, so be warned there. Also, if you are restoring a DB, the database name will be master (from what I have seen, it was a fresh restore). If you are just restoring logs, it will show you the database name of the database where the logs are restoring.
Take this and create a quick SSRS (Reporting Services) report. Or even better schedule this to run every 2 minutes or something and maybe once it sees some records, kick off a report, or make a cool little desktop app that has balloon popups and statuses, so as DBA you can see what is going on with your restores. Maybe I will create that app one Saturday if am bored or something 🙂
One reply on “SQL 2005: Using Data Management Views (DMV's) to View Status of Database and Log Restores”
I believe the reason that it still took 20-30 seconds for you was the fact that your EstimatedEndTime expression was being evaluated as an integer.
Change it to (estimated_completion_time/1000/60.0) to get it to be evaluated as a float or show the end time in seconds…