April 21, 2011 at 8:54 am
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
April 21, 2011 at 10:21 am
How much Processors and RAM allocated for VMWARE,which type of drives you have ,is there any RAID Level or SAN or NAS drive
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 21, 2011 at 10:35 am
Just a hint, but we have a san here and restoring a 2.8 GB would be counted in seconds, not minutes / hours.
So that would be 1 of the first things I'd be looking at.
April 21, 2011 at 12:40 pm
Thank you both 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
April 21, 2011 at 12:42 pm
PS to answer the questions posed:
2 processors, 2 Gb RAM and a SAN drive.
April 21, 2011 at 2:21 pm
2GB of ram seems extremely low especially for a 78 GB db.
We have a "low" use server here with only 18 GB of data in it, but even with 4 GB ram we see quite a bit of activity on the disks.
I don't know what figure to recommend to you, but I'd look into that.
April 22, 2011 at 7:09 am
I totally agree with you. And this isn't really a low use environment. In fact one of the two dbs is a blobs db (images).
But it IS government, and the ones controlling the purse strings are being a bit "tightwaddy" right now. So I live with 2Gb on this one.
April 22, 2011 at 7:22 am
2GB may or may not work, it's all about performance. Depends on the access patterns and load for data. If you can accept the disk delays, it's fine. I might push for 4GB every time someone complains, but I certainly understand budget pressures.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply