locked
Excessively Long Restore Times RRS feed

  • Question

  • Hi,

    I am attempting to restore two databases that are both approximately 78 Gb in size. Generally, this has been taking approximately 30 to 45 minutes. Recently however, behavior has changed and the restores take so long I kill them off. The latest attempt took over 16 hours to restore 19%.

    Here are some more background details:

    * Environment used to be in a HyperV virtual server.
    * Moved environment from HyperV to VMWare.
    * There are two nodes, one active one inactive (but not true failover clustering)
    * Created 1st Node in VMWare.
    * Cloned 1st Node, and used to generate 2nd Node.
    * On 2nd Node, uninstalled SQL 2008
    * Reinstalled SQL 2008.
    * Restored 2 78 Gb databases to 2nd Node. Process takes under 2 hours.
    * 2 weeks go by, have to re-restore both databases.
    * Kick off restore (WITH STATS = 1), 3 hours pass, no stats return. Kill restore.
    * DELETE previous database, manually delete all db files.
    * Kick off Restore again. 16 hours later, restore is at 19%
    * During the restore, I noticed 6 processes attributed to a single SPID.
    3 of those processes had wait types of: PREEMPTIVE_OS_WRITEFILEGATHER
    3 had waittypes of BACKUPTHREAD.
    * Review of comments I discovered by googling the PREEMPTIVE wait type led me to this document:
    http://msdn.microsoft.com/en-us/library/ms175935%28v=SQL.100%29.aspx
    * I verified that the recommendations contained therein, namely to ensure that the userid running the restore was added to the Perform Volume Maintenance Tasks security policy.

    Does anyone have any ideas what may be causing these extraordinarily long restore times?


    Eric Hays-Strom

    Thursday, April 21, 2011 3:06 PM

Answers

  • Have you precreated the databases? The restore process has several steps, 1) sizing the database to restore to 2) restoring the data, 3) restoring the log 4) finalizing.

    YOu can minimize the lenght of 1 by presizing the database. I am not sure what portion of the total restore time this will be.

    Next you have to examine wait statst to see what it is waiting on, or run sp_who2 or query sys,sysprocesses to see if it is consuming io or cpu. Also check to see if a process is blocking it.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Proposed as answer by retracementMVP Friday, April 22, 2011 10:24 AM
    • Marked as answer by WeiLin Qiao Tuesday, April 26, 2011 6:12 AM
    Thursday, April 21, 2011 3:45 PM
  • Mr. Cotter, thank you for taking the time to respond to my request.  I apologize for not getting back to you sooner.

    We have resolved this issue.  Before addressing the resolution, let me add some information that I SHOULD have included in the initial description.

    When the 2nd node was created, it was created with an IP that was VERY close to the IP of the ACTIVE node.  This nearly caused a catastrophe, so we decided to change the IP address.  So, after everything was installed, we changed the IP address.

    While we were waiting for input to our original question, we decided to try two things.  First, we rebooted the server.  Secondly, we went in to the SQL Server Configuration Manager, and made some changes in there where the original IP was referenced.  We then stopped and restarted SQL.

    One of those two steps resolved our issue, and the dbs restored in a timely fashion.

    Again, thank you for your response, I DO appreciate it.

     

    Eric Hays-Strom

    • Marked as answer by WeiLin Qiao Tuesday, April 26, 2011 6:12 AM
    Thursday, April 21, 2011 6:39 PM

All replies

  • Have you precreated the databases? The restore process has several steps, 1) sizing the database to restore to 2) restoring the data, 3) restoring the log 4) finalizing.

    YOu can minimize the lenght of 1 by presizing the database. I am not sure what portion of the total restore time this will be.

    Next you have to examine wait statst to see what it is waiting on, or run sp_who2 or query sys,sysprocesses to see if it is consuming io or cpu. Also check to see if a process is blocking it.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Proposed as answer by retracementMVP Friday, April 22, 2011 10:24 AM
    • Marked as answer by WeiLin Qiao Tuesday, April 26, 2011 6:12 AM
    Thursday, April 21, 2011 3:45 PM
  • Mr. Cotter, thank you for taking the time to respond to my request.  I apologize for not getting back to you sooner.

    We have resolved this issue.  Before addressing the resolution, let me add some information that I SHOULD have included in the initial description.

    When the 2nd node was created, it was created with an IP that was VERY close to the IP of the ACTIVE node.  This nearly caused a catastrophe, so we decided to change the IP address.  So, after everything was installed, we changed the IP address.

    While we were waiting for input to our original question, we decided to try two things.  First, we rebooted the server.  Secondly, we went in to the SQL Server Configuration Manager, and made some changes in there where the original IP was referenced.  We then stopped and restarted SQL.

    One of those two steps resolved our issue, and the dbs restored in a timely fashion.

    Again, thank you for your response, I DO appreciate it.

     

    Eric Hays-Strom

    • Marked as answer by WeiLin Qiao Tuesday, April 26, 2011 6:12 AM
    Thursday, April 21, 2011 6:39 PM