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.