Restore wait for ASYNC_IO_COMPLETE unending

Question

Restore wait for ASYNC_IO_COMPLETE unending

Paul Fredricks

Mr or Mrs. 500

Points: 523
More actions
March 8, 2007 at 1:23 pm

#117888

I'm trying to restore a 66 Gb database from a single backup file. The restore is lasting over 1 hour and never seems to complete. I've seen that the SS process performing the restore is waiting for ASYNC_IO_COMPLETION.
I'm using the EM Restore Database process and I've set the recovery completion state to "Leave database nonoperational but able to restore additional transaction logs". I have a few T-Logs to restore, but it must start with the DB restore.
It creates the MDF file, but not the LDF file. I'm also restoring to a test server from production, so I've had to "move" the files in the restore.
How long should I wait for this to complete?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

We don't know the specs about your server, nor how you are doing the restore (from tape? from disk? across the network?). So, it's hard to tell how long it will take. I had a 300 GB database that took 6 hours to restore (server had 4 CPU, 4 GB of RAM).

It could well take over an hour. How long did the backup take? And was it done on the same type of server?

-SQLBill

Paul Fredricks Mr or Mrs. 500 Points: 523 More actions · Answer 2

Good points, SQLBill

The server I'm restoring into is a HP/Proliant DL380G2 Dual Pentium III @ 1.3GHZ 1G RAM

I'm doing the restore from the local disk.

The backup took 45 minutes on a Compaq ML570 Pentium III Xeon 701MHz , 3.7GB RAM.

Thanks for the reply!

SQLBill SSC Guru Points: 51440 More actions · Answer 3

What does Enterprise Manager show? Expand the databases and refresh. Is the database in LOADING? If so, it could be done and just waiting the next restore command.

-SQLBill

Ray Mond SSCertifiable Points: 7159 More actions · Answer 4

If you were restoring to a new database, SQL Server would have to first create and allocate space for the 66 GB data/log files, which may take a while. If you run sp_who2, the CPUTime would stay constant while the file creation takes place. Once the actual restore starts, CPUTime starts to increase.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Paul Fredricks Mr or Mrs. 500 Points: 523 More actions · Answer 5

Thanks for all the great replies.

It finished 4 hours and 45 minutes. What threw me was the nearly immediate restore of a smaller database I restored earlier, that led me to believe something was wrong. Then SQLBill described his 300 Gb DB taking 6 hours on a much stronger machine, that put me in wait mode, and low and behold, it finished.

I did observe the "loading" state of the database between DB and t-logs restored with NORECOVERY. The DB icon was shown in gray with the word "(loading)" next to the DB name in EM.

I eventually created a script to restore the DB and T-logs, and forgot to change the last t-log restore to RECOVERY. I ended up deleting the DB and starting over with the correction.

Do you know if there is a way to change the DB state from LOADING with a RECOVERY clause without restoring a T-LOG?

Thanks much for your support!

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Yes, it's been posted on this site several times....but it's just:

RESTORE DATABASE dbname WITH RECOVERY

Replace dbname with the actual database name. Yes, it's that easy.

-SQLBill

Paul Fredricks Mr or Mrs. 500 Points: 523 More actions · Answer 7

Awww, I shoulda checked here first. I did look in BOL, but musta missed it.

Thanks much for your help, SQLBill.

Tom Shoval-302020 SSC Journeyman Points: 81 More actions · Answer 8

When restoring a sql server prior to 2005, the restore first creates the file and writes 0 onto it, just to be on the safe side.

during that phase, there's no progress report.

if nothing else should be working on the system, you could monitor the sqlsrvr.exe process and see how much IO it writes. it corresponds roughly to the size of the database.

it is important to monitor it, as the restore may hang indefinitely without any notifications.

I have seen restores onto existing database taking much shorter time, so you could first create an empty database with files sized like (or bigger) than the size of files in the restore, and then do a restore with replace.

even though the total amount of time you'll have to wait is the same or longer, it helps dividing the process into two separate actions.

markoos1 SSChasing Mays Points: 653 More actions · Answer 9

This could also be from lack of instant file initialization. http://mattslocumsql.blogspot.com/2014/02/why-are-my-database-restores-so-slow.html

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 10

markoos1 (7/19/2016)
This could also be from lack of instant file initialization. http://mattslocumsql.blogspot.com/2014/02/why-are-my-database-restores-so-slow.html

A similar problem, which "Instant File Initialization" provides no help for is the number of VLFs contained in the log file. If the default growths were used, then could be a shedload and that could really slow things down a lot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)