SQL Server is the heart of SharePoint. When it stops, SharePoint dies. Therefore, it is important for you as a SharePoint (Technical) Consultant to know how to install and configure SQL Server for SharePoint. Recently I have experienced a moment of Oops at a customer where they have SharePoint and too little knowledge how to set up and maintain a good environment.
That morning I was busy testing a migration. I was using a backup file to restore it in another farm. This backup file was 100Gb and I copied the file first over to the other SQL Server. Well, half way during the copy process I heard people making lots of noise in there offices and the phone started to ring often. “SharePoint isn’t working anymore!” they said. I started my own investigation. And yes, no response. Logging on to the SQL Server took ages and the ULS showed me lots of “Could not connect to SQL”. When the copy process was completed, SharePoint came back online. Oops…
I logged on the SQL Server and checked its settings. The server is that SQL Server is running on has 16Gb of memory and I noticed the memory settings for SQL Server:
I found the cause. Let me explain.
We have 16 Gb. We don’t want SQL Server to consume all of it, because Windows needs to breath as well. Therefore we set the maximum server memory to a few Gb less. In our case the SQL Server was set to 14000 Mb (approximately 14Gb). This leaves about 2Gb for Windows to continue operating. But what about the Minimum server memory? Many people believe that the minimum server memory setting tells SQL Server how much memory it should use when SQL first starts up, however this is not the case. If Windows needs to reclaim memory from SQL Server, it will request that SQL Server release memory from its control. SQL Server will return the memory back to the Operating System until the amount of memory in use reaches the minimum server setting!
My copy action apparently required Windows to claim memory from SQL Server and SQL Server released it all because the Minimum server memory was set to 0 (which is the default value, by the way). SQL Server could not breath anymore and SharePoint died.
I have checked the other SQL Server as well and sadly enough it had the same settings. I have notified Support that they should change the Minimum server memory setting.
How do i set the min and max memory allocation for sql?
Start SQL Server Management Studio, Connect to your SQL Instance. Once connected, right click your SQL Instance and select Properties. On the page Memory you can edit your Min and Max memory allocation.