Wednesday, March 14, 2012 6:51 PM
I first want to say that I'm not really familiar with SQL or managing a SQL servers, but we have a SQL server that keeps crashing like 3 or 4 times a week. The error say's that the connection from our other server to our SQL is lost.
And I really want to solve this problem but because of the lack of knowledge from the SQL I was hoping with a topic that I can resolve this issue or get in the right direction.
Our SQL server has the current hardware configuration:
- Its running in a VMware environment
- Microsoft Windows Server 2008 R2 (64bit)
- 4 CPU's (Xeon X560 @ 2.67GHz)
- 10GB memory
The SQL server has 10GB memory which is assigned to the SQL management automatically, but this means that other applications that are running in the server can not request memory since its already been taken by the SQL management.
So I was wondering is it smart to give the server for example 10GB but let the SQL management only take 80% from the 10GB, so this means 8GB.
This gives the server room for other request that are coming true so that the server can handle them without crashing.
I hope I have made my self clear and that with the help from you volunteers to make me realize of this is a good idea or not.
Thank you all for already ready this.
Wednesday, March 14, 2012 11:49 PM
If your SQL server has a total of 10GB of memory and you are running other processes how much memory do the non-SQL processes ideally need?
You may want to try limiting the SQL buffer cache to 6GB thereby allowing 4GB for Windows, the other applications processes and non buffer SQL requirements. You can then monitor your system to see if you need to lower this value or you have free memory that you can use by increasing it.
EXEC sp_configure ‘Show Advanced Options’,1; GO RECONFIGURE; GO – Set max server memory to 6GB EXEC sp_configure ‘max server memory (MB)’,6144; GO RECONFIGURE; GO -- Check values EXEC sp_configure; EXEC sp_configure ‘Show Advanced Options’,0; GO RECONFIGURE; GO
- Edited by Sean Massey Wednesday, March 14, 2012 11:52 PM
- Proposed As Answer by Sean GallardyMicrosoft Community Contributor, Editor Thursday, March 15, 2012 12:44 AM
- Marked As Answer by amber zhangModerator Wednesday, March 21, 2012 7:14 AM
Friday, April 27, 2012 12:11 PMDear Sean,
I was forget to say thank you for your explantion and good help on this.
I have set the option like shown above but this didn't resolve the issue so I'm now trying to find the solution with technical support from VMware to find the desired solution.
Aldo I need to say this was also a part of the solution, so there for thank you.