Restore wait for ASYNC_IO_COMPLETE unending

  • 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?

  • 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?


  • 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!

  • 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.


  • 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.

  • 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!

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


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


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

    Thanks much for your help, SQLBill.

  • 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.

  • This could also be from lack of instant file initialization.

  • markoos1 (7/19/2016)

    This could also be from lack of instant file initialization.

    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)

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

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