Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 - Connection timeouts at specific times


SQL Server 2005 - Connection timeouts at specific times

Author
Message
gwgeller
gwgeller
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 138
We are running SS2005 behind our website. We are still using classic ASP. We are experiencing timeouts when trying to open a connection using ado. This started happening around Nov/Dec of 2012. There is no pattern on what days it happens. However the times are always 8:55a, 5:05p and 8:05p. Note these are the time when it happens, but one day it may be 8:55a and the next day 5:05p. Then maybe the next day it happens at all three times. We have a lot going on at the top of the hour, but it's every hour not just these times. Also I do not see any jobs that run only at theses time. It's so frustrating because the times are so specific.

What is odd is we've seen timeouts on queries being run from ASP, but I've never seen consistent timeouts when trying to open a connection from ASP until this started happening. To me that is an important factor. To me it seems a network issue. I want to note too that I'm not seeing any issues with the jobs, only the connections from ASP to our db server. However this could be because our jobs run on a different NIC than our IIS server, for the most part. This again leads me to a network issue.

I have performance monitoring setup some common network, system and sql server counters, but I do not see anything obvious. However I'm not an expert so I may have missed something.

I've also recorded the output of sp_who2 every 10 seconds during these periods. I haven't found the issue, but I do see the connections pile up after the issue has happened. Probably a jump from 40-50 to 80-90 open connections. So something is bogging down or backing up, but I don't see anything even running. Everything is "awaiting command" except for my sp_who2 call. Also there is no blocking going on. Even the lead up to the bog down I'm not seeing any process running a command in sp_who2. Basically what I'm seeing is around 40-50 processes "awaiting command" every 10 seconds and then all of the sudden I'm up to 80-90 processes "awaiting command". One interesting thing I saw yesterday was that there was a 30 second gap, instead of 10, between two sp_who2 calls. So whatever was happening delayed the call. This isn't consistent though.

Based on the performance monitoring and sp_who2 data I don't see anything obvious with sql server. I'm leaning towards something bogging down the network. I might try to pull out the network counters from my performance monitoring and make a separate one so it's easier to view.

Can anyone add some input or point in the right direction as far as what I should be monitoring and what I should be looking for?
gwgeller
gwgeller
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 138
It turns out it was a job after all. I was able to run sp_who2 at the right time and that's how I saw a job was running.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search