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.

Niels Brinch
  • 413
  • 5
  • 16

3 Answers3

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.

Wesley
  • 32,320
  • 9
  • 80
  • 116
Mike
  • 36
  • 2
  • 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 01 '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 09 '12 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.

Community
  • 1
Jim B
  • 23,938
  • 4
  • 35
  • 58
  • 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 08 '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 08 '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 08 '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 08 '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
  • If you are positive that your working set exceeds ram the only solution is to add more ram, a pagefile will not help you. At that point you can only profile the sql server and redesign whatever query using up all 4 GBs, again typically the server crashes long before sql has a chance to error out but perhaps something times out before using up all the ram and then sql can't complete – Jim B Dec 11 '11 at 13:56
  • I am positive that my working set does **not** exceed the ram. It's very small queries and just a few every second. However, SQL Server grabs lots of RAM even though it doesn't need it, so does other processes on the server, such as those used by the OS. Sorry for not being able to phrase my problem better, but thankyou for your attention. – Niels Brinch Dec 11 '11 at 17:00
  • But heck - this is NOT SQL server running out of memory. REEAD. w3wp is the web server, not SQL Server. Quite obviously, 4gb is too small for your sql server AND (!) your web application. If you limit SQL Server 20 2048mb - and get the error, it is time to look at where the error happens (web service). Maybe you cache a lot and blow the memory there? – TomTom Apr 26 '12 at 18:27
  • I actually just have the SQL Server on the 4 GB box, so some of my previous error messages posted here were irrelevant. Sorry for that (it's because I have the same problem on the 2 GB web server) – Niels Brinch Apr 27 '12 at 11:49
  • @tomtom even if he limited sql(based on the symtoms) I'm not sure it would help since sql will only grab free ram (over time). I suspect that since there is no pagefile memory is getting fragmented to the point of being unavailable in chunks that are usabel for the .net heap (which is why there is a heap corruption error) – Jim B Apr 27 '12 at 14:32
0

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

May be it helps...

Regards,

SivaG

sivag
  • 44
  • 3