How can I tell if my restore process is actually doing something

Just stumbled into this: if you have a large DB to be restored, it might take forever to actually see the progress moving to 10%, 20%, and so on. Yeah, I know, you should never do a restore from the SSMS, but don’t try to tell me thatJ.
Anyway, coming back to our scenario: if you have started the restore process, it might take quite a while to see that our particular restore is progressing.
So here’s a quick look at it:

SELECT GETDATE() as runtime, command,
  percent_complete, estimated_completion_time,
  wait_time, wait_type
  FROM sys.dm_exec_requests

You’ll get a whole bunch of stuff, but we’re going to focus on the “command” part of it, because this is of interest for us:

As you can see, there’s a RESTORE DATABASE at the second column (command, in our script).

If I want to focus only on that particular command, I simply add a where clause to the above script:


Of course, in the percent_complete column I get to see something above 0, if everything is ok.

I hope this helps.

Best regards,

Edit: full article here – as usual, PSSQL team rocks.

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.