1

I have a SQL Server 2008 R2 in a Cluster in an active-passive mode. The server is running out of RAM (both physical and pagefile). In the instance level settings there is not Max RAM set (set to the maximum number).

Would the instance failover?

  • Memory is not an option that you would consider for initiating a failover. Its a resource that a cluster does not care - whether it is high or low. – Kin Shah Nov 10 '15 at 20:55
3

SQL Server being low on RAM is not going to be enough of an exception that will cause a failover. It will just start holding up transactions until it can either get more memory from the OS or transaction will fail from timeout.

I would think you could possibly see a failover event occur if the Windows Cluster Service, or dependent services/components, need memory and are not able to get it. I can't recall this happening before on environments I have supported, but think it could happen.

4

Starting with SQL Server 2012 the cluster will have a more comprehensive health check which allows for a possible resource based health condition using the SP_Server_diagnostics procedure. https://msdn.microsoft.com/en-us/library/ff878233.aspx

More on that failover policy can be read about here: https://msdn.microsoft.com/en-us/library/ff878664.aspx#monitor

Unfortunately though SQL Server 2008R2 and earlier doesn't have much of a sophisticated cluster health check for the SQL Service. Simply the health check will just issue the following command "SELECT @@Servername" and if it returns in a timely fashion the health check passes.

I would look instead though at why is the server under memory pressure and setting the max memory setting in the SQL Server instance putting a cap on that memory foot print.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.