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 have a SQL Server 2005 server that's been running fine most of the time.

Today the users started to get time outs when opening new connections, including myself when I tried to open another tab on SSMS.

I've looked everywhere for the problem but I haven't found anything actually wrong with the server!

It's over 10.000 seconds of PLE, >99.80 cache hit ratio, Target Memory of 10GB and using only about 4GB. The server has 16 processor and the load is well below 20%. The I/O is pretty low, most activity comes from logging and CHECKPOINTs and only rarely the Lazywriter has to write stuff to disk.

This server is always been pretty underutilized, and still today I started getting timeouts when opening new connections.

The server has been restarted and the problem persists.

My first thought would be to blame the network, but I have remote DAC setup and I've been able to connect to the server without any delay when using the DAC, so I'm not so sure it's the network.

How can I track this problem down and diagnose what's going on? What tools or techniques can help me solve this?

share|improve this question
    
Also forgot to mention I've started a trace and found some Lock Timeouts on tempdb and a database called ASPState that's used by the ASP.NET server. –  ivanmp May 15 '12 at 2:00
    
Can you post the exact timeout message you are receiving? And whether you are getting these just in the application, or if you're seeing them in the SQL Server error log and/or the server's event viewer? –  Aaron Bertrand May 15 '12 at 2:02
    
@AaronBertrand I'm seeing them in the applications and in SSMS. No mention of timeouts on the error log. I'll post the exact messages as soon as I can (I'm not at work right now), but it's the standard timeouts you get in SQL Server Management Studio, for example if you try to connect to a SQL Server instance that's offline or doesn't exist. –  ivanmp May 15 '12 at 2:06
1  
Have you tried fully qualified name, server name, and IP address? Is it a named instance? Is SQL Server Browser running? Are you using TCP/IP or named pipes? Is there a firewall in place that might be blocking normal ports but allowing DAC traffic through? –  Aaron Bertrand May 15 '12 at 2:09
1  
If you don't have network staff available, then I can only suggest high-level reading (I am not a network guy at all): technet.microsoft.com/en-us/magazine/2007.12.network.aspx blogs.msdn.com/b/sudeepg/archive/2009/02/10/… –  Aaron Bertrand May 15 '12 at 14:58

1 Answer 1

up vote 2 down vote accepted

Have you checked the error log? In Object Explorer, expand Management, expand SQL Server Logs, right-click Current... and choose Show SQL Server Log. Hopefully there will be more details there about what is causing the timeouts. If you can post anything suspect that you find there, we can help you troubleshoot further (though it may be something obvious, like a logon trigger or resource governor).

share|improve this answer
    
Sure, it was the first place I looked. Forgot to mention, but it didn't have any mention of any error during the time the timeouts started. –  ivanmp May 15 '12 at 1:58
    
I'll mark your answer as accepted because you've helped me through the process of diagnosing the real issue, even if your answer is not actually THE answer to the problem. Thank you! –  ivanmp May 15 '12 at 19:30
    
You can always edit my answer so that it includes the relevant information, however I don't know how useful that will be to future readers depending on your config, the popularity of the AV program you're using, etc. Sounds a little obscure. –  Aaron Bertrand May 15 '12 at 19:31

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.