Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

3 Answers

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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