I am unable to connect to the SQL Server instance. Logging into the box remotely I can see both CPU and MEMORY are maxed out. I have tried to restart the SQL Server service which has resulted it in "change pending" state which just seems to hang.
There are 2 SQL instances on this server, the other is operating fine. There are also Windows error logs for this instance which read
There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
Any ideas?
Best Answer
Need more info like SELECT @@VERSION, do you use CLR procs, have you set max memory on both the instances? Were you able to look at which spids were taking most resources and why?
To catch the intermittent cpu spikes, you may have to setup a trace and here is one such script.
http://www.sqlsoldier.com/wp/sqlserver/catchingtransientcpuspikesusingsqltrace