2

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?

NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
ivanmp
  • 2,302
  • 3
  • 23
  • 35
  • 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 02: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 02: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 02:06
  • @AaronBertrand sometimes SSMS hangs a little longer but connects successfully and sometimes it times out, and I have to connect again. – ivanmp May 15 '12 at 02:08
  • 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 02:09
  • Intermittent definitely sounds like network to me. – Aaron Bertrand May 15 '12 at 02:09
  • @AaronBertrand It's a default instance running alone on the server. Yes, it's all configured by the guys responsible for the infrastructure and it's been running for many years without a problem. So we are back to network... – ivanmp May 15 '12 at 10:33
  • @AaronBertrand this is the exact error I'm getting: http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2 – ivanmp May 15 '12 at 12:20
  • Still seems to me like the app and SSMS can't reach SQL Server. So this isn't going to be a SQL Server problem explicitly, but a network or port or firewall issue. Especially, as I said, if it is intermittent. You didn't answer my question about whether you are using a fully qualified name, a simple server name, or an IP address. Also whether the same situation applies with both Windows Auth and SQL auth. It could be a resolution problem checking the machine/user/both with Active Directory. Ask your network/sysadmin ppl if anything has changed recently with the domain controller (and try IP). – Aaron Bertrand May 15 '12 at 12:41
  • The problem occurs regardless of whether I use the server's simple server name or IP address. It occurs with both SQL Auth and Win Auth. I noticed that the problem DOESN'T occur if I use the Named Pipes protocol, which is really strange. I don't think it's a firewall issue since it's intermittent. I should add that I really appreciate your support, thank you. – ivanmp May 15 '12 at 14:54
  • 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): http://technet.microsoft.com/en-us/magazine/2007.12.network.aspx http://blogs.msdn.com/b/sudeepg/archive/2009/02/10/using-microsoft-network-monitor-to-track-down-networking-problems.aspx – Aaron Bertrand May 15 '12 at 14:58
  • @AaronBertrand We've finally managed to convince the infrastructure staff that the problem was NOT in SQL Server, so they started checking and it was found that the AV was analyzing every and each connection to SQL Server via TCP/IP, and the problem was solved. Thank you very, very much for your support! – ivanmp May 15 '12 at 19:27

1 Answers1

3

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).

Aaron Bertrand
  • 175,645
  • 25
  • 380
  • 589
  • 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 01: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