How to look at what is going on in a pegged SQL Server instance?

Question

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

Do you ahve aresource governor active? That would handle the pegging by slowing down this particular processing. — TomTom, Mar 08 '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 08 '11 at 18:11
By default DAC is only listening locally. Must connect from the same host. — Remus Rusanu, Mar 09 '11 at 05:46

score 2 · Answer 1 · answered Mar 08 '11 at 16:47

2

Try the Dedicated Administrator Connection (DAC).

answered Mar 08 '11 at 16:47

JeffO

181
4

Hey Jeff, I updated my question with the error that was thrown when i tried that. – Abe Miessler Mar 08 '11 at 17:27
That is likely jsut a config issue which should have been fixed first. – TomTom Mar 08 '11 at 17:37
@TomTom, what kind of config issue? – Abe Miessler Mar 08 '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 08 '11 at 19:05

score 0 · Answer 2 · answered Mar 08 '11 at 16:34

0

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.

answered Mar 08 '11 at 16:34

Brosto

113
5

No, I can't log in. – Abe Miessler Mar 08 '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. – Mar 08 '11 at 16:48
Sorry, I can log onto the machine, but not the SQL Server instance. – Abe Miessler Mar 08 '11 at 16:50

score 0 · Answer 3 · answered Mar 08 '11 at 18:08

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.

score 0 · Answer 4 · answered Mar 08 '11 at 20:38

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.

How to look at what is going on in a pegged SQL Server instance?

4 Answers4

Linked