4

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.

3 Answers 3

2

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.

3
  • 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. Apr 27, 2012 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? May 1, 2012 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. May 9, 2012 at 17:30
1

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 https://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.

10
  • 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) Dec 8, 2011 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. Dec 8, 2011 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. Dec 8, 2011 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, 2011 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. Dec 11, 2011 at 13:44
0

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

May be it helps...

Regards,

SivaG

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .