SQL 2005: Using Data Management Views (DMV’s) to View Status of Database and Log Restores

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:

use master
go

SELECT
    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 thought on “SQL 2005: Using Data Management Views (DMV’s) to View Status of Database and Log Restores”

  1. 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…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s