Problem
What happens when you have a SQL Server 2016 machine that is setup in a Cluster, but the SQL Server Service consistently restarts or fails over to the passive node?
Solution
We recently had an issue with a newly minted SQL Server 2016 clustered setup. The server had been up for a few months without many issues. Suddenly, the SQL Server service began to terminate and to restart intermittently. Within the span of few hours, the service would restart up to 100 times.
Furthermore, the server would often failover to the passive node, sometimes when the SQL Server Service was restarted or even other times that seemed equally random.
As we tried to troubleshoot the issue, we looked at everything from the Virtual Machine (VM) which housed the server to the operating system, to Operating System patches, to cumulative updates for SQL Server. The Server was Windows Server 2012 with all the current patches along with SQL Server 2016 with SP1 installed. One of our first steps was to install the most recent Cumulate Update for SQL 2016, but unfortunately, that did not help.
We even saw several network related errors that popped up.
We checked the memory allocated to SQL Server, which looked acceptable for a server with only SQL Server on it.
Additionally, we checked and validated many other server settings such as parallelism and network packet size.
To be honest, we were at our wits end as nothing was indicating what the problem could be. Of course, we were researching extensively online, but nothing in particular surfaced as an indicator. I was ready to blame the VM, the data center, the SAN, anybody, but then I saw it.
Someone had checked the Boost SQL Server priority checkbox!! I know that property stands out as a “GO Fast” button like running queries with NOLOCK. I also know that it is BAD, very bad in most cases to use as SQL Server will, in essence consume all the resources on the machine. We unchecked the “magic Boost SQL Server priority” option, and then suddenly all went calm. Well not immediately, but it did solve the SQL Server Service restart and failover issues pretty much immediately. We are trying to ID the “perpetrator” who fell for the magic button bait and checked that box to begin with! No suspects have come forward yet!
Next Steps
- Please see the many other configuration tips available at MSSQLTips: Read More SQL Server Configuration Tips
I have a passion for crafting Business Intelligence Solutions for my user groups. My experience includes almost 15 years of SQL Server involvement with the last 12 years focused specifically on Business Intelligence, SharePoint, OLAP, SSRS, and Decision Support solutions. Currently, I am a Business Intelligence Architect in the healthcare industry, and I also teach database and analytics classes for Kennesaw State University, Southern New Hampshire University, and Reinhardt University. My education includes an MBA and an undergraduate in Accounting (yes I am a reformed accountant!), both from Kennesaw State University. I enjoy every day by trying to grow my faith and spend precious time with my family. I have been happily married to my wife of over 20 years, and we have two teenagers one who we home school with the help of a University Model School, Cornerstone Prep in Acworth, GA (cornerstoneprep.org). Our other child is a Construction Management major at KSU’s Southern Poly / Marietta campus. We are a soccer and Cross Country (XC) family who play, coach, and referee soccer or run for fun most every day. For several years, our family has volunteered (and played with the dogs and cats) at Etowah Valley Humane Society in Cartersville, GA.
- MSSQLTips Awards: Champion (100+tips) – 2016 | Author of the Year – 2015 | Author Contender – 2014, 2016-2021