Unexpectedly long duration on Restore, no feedback.

RichardB

Hi

Current issue: Restoring a 200GB database over vpn. Seems to be taking much longer than expected, and I can't see any feedback on progress.

DB is marked as loading, query is still 'running', but I can't see any activity. The physical database files were last updated (os level) a couple of hours ago.

I am concerned because the log shipping broke a while back, this is the recovery. The log backup drive ran out of space resulting in a bunch of 0 size files going across. Most of the dbs recovered with a little jiggery pokery, but not this one - this got stuck in a perpetual restore - possibly something to do with the 1gb or so transaction log backup...

petey

If you run the sqbstatus extended stored procedure (found in the master database) periodically, do the numbers increase?

RichardB

Ah, excellent, yes they are. This presumably gives us confidence it is still working...

What do they represent? Am I right in assuming that Processed(Bytes) is the amount that has restored (/1024/1024/1024 to get gb? - on 60 ish and 20 after 5 hours)

The database being restored has 2 filegroups + logs - are they treated as a whole in these numbers?

Cheers for the response,

Rich

petey

Yes, 'Processed (bytes)' represent the uncompressed size, while 'Compressed (bytes)' represent the amount of compressed data that has been processed. All numbers are in bytes. And they are indeed treated as a whole.

I should have mentioned that both numbers may also be 0 initially. This is because if you are restoring a non-existing database, SQL Server will create a 200 GB file at the beginning. Once that file has been created, SQL Backup will proceed to uncompress the data. Depending on your disk speed, the creation of that file by SQL Server may take a while.

RichardB

Great, thanks pete.