Restore wait for ASYNC_IO_COMPLETE unending

  • Paul Fredricks

    SSC-Addicted

    Points: 463

    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?

  • SQLBill

    SSC Guru

    Points: 51440

    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

    SSC-Addicted

    Points: 463

    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

    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: 7099

    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

    SSC-Addicted

    Points: 463

    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

    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

    SSC-Addicted

    Points: 463

    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

    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

    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: 993661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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