Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have rented a virtual server with 4 GB memory from Hosteurope, including a license for SQL Server 2008 Workgroup Edition. It works alright and is plenty fast, but whenever I put some load on it, it will run out of memory and as a result, the server will crash.

It should be mentioned that Hosteurope has a system for their virtual servers which does not allow for swapping to be used on the server. That's why there is no pagefile and when it's out of memory it's completely out.

Hosteurope refuses to help or take any responsibility, but I really hope someone here at serverfault can help me.

I already tried setting the "Maximum server memory" setting in SQL Server to 2000, but it doesn't make any difference.

Symptom of the problem The database server stops accepting connections.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

  • Restarting the SQL Server server does not fix the problem.
  • Restarting the web server does not fix the problem.
  • Restarting the complete server where SQL Server is installed, fixes the problem.

...until next time there is a bit of load on it, then it stops working again.

So now I kindly ask you for:

  • Help to figure out how I find the exact errors that causes this behaviour. I've looked in the event log for both system and application, but neither shows anything conclusive.

  • Help to figure out how to configure my server/sql server installation so it will not crash.

Primary solution Do not run SQL Server on a Windows Server with swapping/page file disabled if you wish to put load on it. This means, do not choose HostEurope as your host if you wish to put load on your DB server!

Secondary solution Improve reliability by limiting the amount of parallel workers on the SQL Server as explained in the accepted answer below.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

I have exactly the same virtual server from Host Europe as well as the same problem. I think I have found a solution.

It seems to me that parallelism on the server results in this issue. The virtual server has 24 virtual CPUs and this is too much for 4 GB of RAM. I made the following changes in the configuration of the SQL-instance (I have a German system I don't know the exact text for an English SQL-Server):

  1. Open Properties for the instance in Management Studio.
  2. Go to the "advanced" tab.
  3. Change the option for maximum parallelism from 0 to 2.
  4. On the memory tab, change the maximum memory for the server to 1792 MB.

This appears to have solved my problem.

share|improve this answer
    
Wow, that sounds like a great and very probable solution! Thankyou so much - I will try that out and report back here on whether it worked. –  Niels Brinch Apr 27 '12 at 11:33
    
I don't really know - it still crashes but maybe a little less. I'm not sure. Can you remember if you tweaked anything else? –  Niels Brinch May 1 '12 at 17:06
    
It seems to work a little more stable now... but still crashes if I put load on it. It's been running for 3 days now, though, the best so far. –  Niels Brinch May 9 '12 at 17:30
add comment

First - windows doesn't swap per se, its a little different (and if you run perfmon on you existing system you'll notice it still does page fault even without a pagefile) see http://serverfault.com/a/75027/3528 for details.

You said that the server runs out of memory when you put load on it. Do you have some logs or evidence that it's running out of memory. Typically horribly bad things go wrong long before sql server has a chance to deny you access if you were truly out of memory. There should be some events in the event log that will give you some clue as to what's happening.

share|improve this answer
    
This is from a webserver I also have with Hosteurope and it also runs out of memory: Exception information: Exception type: OutOfMemoryException Exception message: Exception of type 'System.OutOfMemoryException' was thrown. at System.Runtime.CompilerServices.RuntimeHelpers._CompileMethod(IRuntimeMethodInfo method) at System.Reflection.Emit.DynamicMethod.CreateDelegate(Type delegateType) –  Niels Brinch Dec 8 '11 at 17:02
    
Followed by: Application: w3wp.exe Framework Version: v4.0.30319 Description: The process was terminated due to an internal error in the .NET Runtime at IP 000007FEBC677DFC (000007FEBC0A0000) with exit code 80131506. –  Niels Brinch Dec 8 '11 at 17:03
    
From the post you referred to he writes "This also means that "running out of RAM" seldom results in an “out of memory” error. Instead of an error, it results in bad performance". However, in my case it always results in an error because there can be no pagefile. –  Niels Brinch Dec 8 '11 at 17:09
    
So clearly, when the error says out of memory, you are out of memory. This may or may not have anything to do with the lack of pagefile. If your working set exceeds ram you are out of memory. –  Jim B Dec 8 '11 at 23:30
    
I know, I am out of memory. Now, my target is to find a reliable way to make sure that doesn't result in the server giving out errors and eventually (effectively) crashing so that no new connections can be made. I prefer that it functions maybe slightly slower over it crashing. –  Niels Brinch Dec 11 '11 at 13:44
show 5 more comments

Enable max server memory in configurations assign 3GB for sql server and 1gb for windows server .

May be it helps...

Regards,

SivaG

share|improve this answer
add comment

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.