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'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,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
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?

share|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 Jan 29 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 at 13:16

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.