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?

  • The hanging service has finally stopped. I have manually started it again. All seems fine (for now). – jsauni May 17 '11 at 0:22
  • What memory/32 or 64 bit/using PAE etc? – gbn May 17 '11 at 7:07

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.


  • One is a 2K5 instance the other 2K8. They do not use CLR procs (as far as I'm aware). What is the best practice for setting max memory for 2 instances (or more) on a single server? – jsauni May 17 '11 at 0:22
  • 1
    Identify how much is needed for each instance and set it. Say the box has 12 GB, and Instance1 needs 4 GB, OS needs 2 GB at-least and the leave the remaining 6 GB to Instance2. make sure you have no other apps running on that box. Is yes, account for memory usage for that also. If you are using Standard edition, then use Lock pages in memory and a TraceFlag 845 IIRC is necessary. – Sankar Reddy May 17 '11 at 0:37

In this scenario, try a DAC connection. That will at least enable you to do some diagnostics, and perhaps even to clear them (e.g. by killing a session that has gone beserk) without restarting.


Sometimes there may be a runaway process eating up the CPU and preventing connections. Try: select * from sys.sysprocesses order by cpu desc run this a few times to see the changing counters. If there is a process that is consistently a the top, it is a sign of that process eating up the CPU. If you can kill that process, CPU usage will come down and you can connect again normally.

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.