I'm attempting to determine root cause for an issue that caused an outage for my company.

Essentially the site connections to our database server were timing out after not receiving a response for 5 min. A check of perfmon counters showed that the database server was using about 10% of processor, memory was under utilized, and our hard disks were not being stressed.

A check of long running queries using the following statement showed that there were a lot of queries queued up with a status of suspended the longest of which had been running for 50 minutes.

SELECT sqltext.TEXT,
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

There were also several queries with a status of running but none of these had been running for a significant amount of time.

SQL Server threw no alerts during the time frame and nothing looked out of the ordinary in the logs. There were also no alerts for Blocking processes at this time. Finally the decision was made to fail over the SQL Server cluster.

This cleared up the issue.

I've exhausted the places I can think of to look to come up with an explanation for the outage. Has anyone experienced similar behavior? Is there something I should be checking that I haven't mentioned?

| improve this question | | | | |
  • To me looks like thread starvation. What is the maxdop setting? I have encountered on one of my server when it had maxdop set to default (use all the threads). Have you checked windows event logs as well as logs folder to see if any mini dump was created ? This is just an assumption as you don't much data to find out the root cause. Also check the cluster logs. – Kin Shah Jan 29 '14 at 3:55
  • Maxdop is set to 6. The network admin said that he didn't see anything in the windows logs. I'll have to find the cluster logs. I'm not certain if those were checked. – Lumpy Jan 29 '14 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.