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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.