Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the progress of where the restore is at, and it hangs at various times. One time it may complete 56%, the next time 64%, the next time 17% and so on. It does not stop at consistent times. And when I say that it hangs, it just stops writing. As far as SQL Server is concerned, the restore is still running though. So its like it still doing its thing, but nothing occurs. If you leave the restore running without killing the process it will run forever. I have let it run for 12 hours before with no progress

Eventually, I can get the restore to complete after trying again over and over, and eventually it will go one time and finish. But it takes what should only be an hour, and turns it into 5 hours of constantly retrying.

So here is some information on the server -

It is a Virtual Machine,

Windows Server 2008 R2 Standard, SQL Server 2008 R2 sp2 Standard, 32 GB RAM, 4 Proc Here are the database sizes that are the problem. While not really, they are not overly large either.

Database A - 71 GB, Database B - 11 GB, Database C - 27 GB

Here is what I have done:

I have checked all the logs (SQL Server and Windows) to look for any errors or any messages that looked suspicious. There is nothing.

I have tried to run the restores at nearly every hour of the day, but it does not matter, it will hang at any point.

I have tried dropping the databases\deleting the .mdf & .ldf files and recreating the databases and then restoring, still get the issue

I have traced the restore to see if anything is occurring. Nothing.

I have made sure all connections where killed before starting the restore. Also, have placed the databases in single user mode before starting the restore.

I have no problems restoring the exact same backups on other servers, so no issue with the backups

Storage guys looked at the datastore\LUN and checked NAR files and said everything looked good.

They also migrated the vmdk to another LUN, but we still got the issue.

Storage guys sent the NAR files from the Array to EMC for examination and they say no issues with the storage

VMWare guys looked at the Virtual Machine and saw no issue with it.

I also disabled all the Anti-virus software and then tried it again, still will hang

I have ran the restores using the GUI, writing out the script, and using custom scripts, same result

Ran a repair on SQL Server

Nothing unusual when looking at the Wait Stats. Shows same thing as you would see when doing a successful restore.

Any thoughts on anywhere else to look or possible reasons here. Or any tracking or counters that you can recommend to pinpoint the issue.

My thoughts are an issue with storage or the server, but the Ops guys are adamant there is no issue with either of those.

Also, rebuilding this server at this time is NOT an option. Even though it is Test, it is part of a farm of servers that makes up a Claims environment. We will eventually rebuild this entire environment later this year, but it is a large undertaking.

The next step is getting on the phone with Microsoft, but waiting on the Ops guys to take one more look. So thought I would check here.

Thanks for your help!


Results of requested waitstat script -

enter image description here

share|improve this question
    
can you post: The script used to create the backup. The script used to restore the backup and the waitstats of the server while it has been hanging for a long time. with for example this script: sqlskills.com/blogs/paul/… –  Edward Dortland Jan 24 at 14:09
    
does the SQL Service account have "perform volume maintenance tasks" permissions and when you start the restore, are the mdf and ldf files of the database that you are restoring the database over a representative size? or do they need to grow a lot before the backup that you are trying to restore "fits" ? –  Edward Dortland Jan 24 at 14:12
    
Edward - The backups are created using maintenance plans and the restores have been done every way from using a custom restore script, writing out the restore script, and using the UI with the only thing selected being to "overwrite". I can post the scripts, but they are pretty general. For the wait stats I added a screenshot that shows what they are currently. I just kicked off a restore that got stuck and captured these - Restore got stuck at 45.56% –  user3224291 Jan 24 at 14:37
    
The Service account is an admin on both the server and in SQL. These databases are replicas of our production databases, so they are very close to the same size. Also, I have scripted out the databases from production including current size and created the files on this server so size is matched up, and it doesnt matter –  user3224291 Jan 24 at 14:40
    
Can you rule out SQL Server by just copying a large file from the same location as the backup files to the same location as the mdf and ldf files. (and if these mdf and lfd are on different disks, just perform the test two times..) –  Edward Dortland Jan 24 at 14:44

1 Answer 1

I think I have found the issue.

The backups are stored our data domain, so when performing the restore we select the backups from this location. I think there is some type of connection issue between this server and the data domain. Because when I move the backup files over to the server and restore from the server directly, I cannot reproduce the issue and the restores are probably 10x faster.I set a continuous restore of a database (11 GB) that has been running for about 25 minutes and has completed the restore successfully 23 times with no hanging

My thinking is that the connection issue probably lies somewhere with our test server still. Because we use the Data Domain for all of our production backups (although it is changing) and have no problems restoring elsewhere, including the databases I have been working with. Not to mention, we have automated nightly restores on other servers that have never failed.

I think this at least pinpoints the issue. I will look into where the connection issue is and work with the server\networking guys to see if we can resolve it.

Thanks you guys for your quick assistance

share|improve this answer
1  
So you were restoring it over from the n/w ? –  Kin Jan 24 at 17:05
    
Yes, it was being restored over the network. But really not a lot of alternative choice since the backups are large enough that we do not have a lot of additional space on the server. Still an issue, but at least can see where it is at –  user3224291 Jan 24 at 17:13
3  
You missed to include this important piece of information in your question. Any ways, you figured out the problem. Sage Advise : Never try to restore this big database from the n/w. You will end up corrupting the database as well. –  Kin Jan 24 at 17:52
    
Where do you store your backups? Directly on the server or on a thumb drive sitting next to your PC. That is a pretty ridiculous comment. That is the way these things are designed. You backup and restore from the place your backups reside (in this case Data Domain, a specific solution to do this). In restoring probably thousands of databases in my career, I have never once seen corruption or problems from restoring from shares. –  user3224291 Jan 24 at 19:21
4  
Its not a ridiculous comment. Just a suggestion - up to you how you take it. I was pointing out that always copy the backup locally and then restore. A n/w glitch will cause you problems. Anyways, we are here to help and get help ... So nothing personal. –  Kin Jan 24 at 19:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.