Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a SQL Server instance that is pegging out my server. I'd like to look at what jobs are running, but it's so slammed that I can't even log in.

Is there some secret way to get in and look at whats running when the server is slammed or do I just need to yank the plug out of the wall?

UPDATE: I tried connecting using DAC but it threw this error message:

enter image description here

share|improve this question

migrated from stackoverflow.com Mar 8 '11 at 17:16

This question came from our site for professional and enthusiast programmers.

    
Do you ahve aresource governor active? That would handle the pegging by slowing down this particular processing. –  TomTom Mar 8 '11 at 17:37
    
The resource governor is allowing 100% across the board. Even if I knocked it down I still wouldn't be able to connect to my SQL Server instance correct? –  Abe Miessler Mar 8 '11 at 18:11
    
By default DAC is only listening locally. Must connect from the same host. –  Remus Rusanu Mar 9 '11 at 5:46

4 Answers 4

Try the Dedicated Administrator Connection (DAC).

share|improve this answer
    
Hey Jeff, I updated my question with the error that was thrown when i tried that. –  Abe Miessler Mar 8 '11 at 17:27
    
That is likely jsut a config issue which should have been fixed first. –  TomTom Mar 8 '11 at 17:37
    
@TomTom, what kind of config issue? –  Abe Miessler Mar 8 '11 at 18:11
    
Disabled admin connection, firewall, anything like that. Did you actually bother to read the error message and do what it say (check the error log for the port number)? –  TomTom Mar 8 '11 at 19:05

Can you get into SSMS? If so, have you tried running sp_who2?

that should show you what SPID's are eating up the most CPU.

share|improve this answer
    
No, I can't log in. –  Abe Miessler Mar 8 '11 at 16:40
    
So you can't log into SQL server, and you can't log into the machine itself? What about from a console session on the machine? I'm not sure of any other ways, but would be interested to find out if there is. –  Brosto Mar 8 '11 at 16:48
    
Sorry, I can log onto the machine, but not the SQL Server instance. –  Abe Miessler Mar 8 '11 at 16:50

I would check the event logs on the server, see if there's anything going on with disk errors or anything else that might be out of the ordinary.

If you're not able to connect to the instance, even through the DAC, your options are limited, wait, or stop the sql service and restart it. If you're not sure where the activity might be coming from and it will help with troubleshooting, you could do a "netstat -an" and capture to a text file for analysis, that will show a line for each connection to the server.

After you can connect, check the SQL error logs for anything unusual, and check the default trace files for any help they can provide.

share|improve this answer

If you can't log into the SQL Instance from the servers console then the only option that you have is to restart the SQL Service. After you do, enable the Dedicated Admin Connection and restart the service again so that the next time the service locks up you can log in via the DAC and see what's going on.

share|improve this answer

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.