Post reply

sql server slow to accept TCP connections READ

  • July 20, 2009 at 12:40 pm

    #132103

    I am helping a client with an SQL server. This is a very odd issue but has happened 3 times in as many months. When using Studio manager (any connection method really) the login times out. This server is not busy, 1% cpu usage, no disk bottleneck, no memory issues. This server is primo! To resolve the login timout i bumped up the timeout in SSMS 5 seconds at a time until it connected at about 28 seconds (30 second timeout setting).

    So that's the scenario we can connect but it takes 28 seconds. If i change the port number to any port other than the port it is on right now it works fine and you can connect instantaneously. Some weeks later, the timeout issue will come back. Simply changing the port repairs the problem. I know some may be saying, well to change the port you stop the service and restart, true, but if you simply stop and start the service it doesn't help. If you reboot the server it doesn't help. Only if you change the port does the problem go away.

    This server has 2 instances, only 1 has this strange issue. Host A connects to instance A, this bad instance, host B connects to instance B the other instance on the same server. Only HOST A\Instance A have the issue. Also it only seems to have the issue from one unique host (the app server) host A. All other machines on the network that we connect with, when testing, work fine. Although, only one host A connects to this instance in production.

    My next steps are next time this happens to get out the packet sniffer and see if i see anything odd, i would presume i will see the packets hitting the port but SQL server is not replying to them.

    Lastly if you just use telnet to the port to do a basic connection test it also experiences the timeout meaning it wont connect. So it doesn't seem to be a .net issue either.

    Any help would be appreciated.

    Jimmy

    "I'm still learning the things i thought i knew!"

  • September 10, 2009 at 1:35 pm

    #1051928

    Anything on this....I'm having the same issue.

    Thanks

  • September 10, 2009 at 1:39 pm

    #1051929

    The issue has not come back. I have no further info.

    Jimmy

    "I'm still learning the things i thought i knew!"

  • September 10, 2009 at 1:56 pm

    #1051941

    i would say make sure that both instance are not using dynamic ports. . if they are using dynamic then change them.

    also its not do you see any error in error log or eventviwer that will help also.

    :crazy: :alien:

    Umar Iqbal

  • September 14, 2009 at 12:52 am

    #1052971

    Hi!

    Try these steps.

    1. Connect sepcifying Ip adress and port.

    eg. 192.168.1.10,1207

    2. Check firewall is blocking the port.. if so give exceptions for that port in firewall settiongs. and start sql srvice servcie on that port.

    regards

  • September 14, 2009 at 1:17 am

    #1052975

    check the firewall for port and exception settings. And, also do a telnet from command prompt for the port in question.

    TELNET serverIP port#

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • September 14, 2009 at 1:41 am

    #1052986

    You dont mention the version of sql server or the build. there could be a fix perhaps?

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • September 14, 2009 at 6:11 am

    #1053122

    Thanks for the suggestions...there is no firewall or port restrictions on our internal network (this is all internal).

    I did change to static port from dynamic and it seemed to help but we still got the timeout errors for the one Instane from our Sharepoint (there are 10 different instnaces on this box).

    We have a lot of network traffice from this box as we constantly sync backup directories from the 10 different Instances to a network drive using Robocopy (every instance, every 3 min.). This has not been a problem for months until this problem started showing up last week. I did notice when they took down the network drive to apply patches (took 30 minutes)...all of a sudden we got a lot of connection errors to that sql server instance....we knew it would disrupt the robocopy executions...but perhpas that disruption also caused problems with network traffic. Not sure.

    So we backed off the robocopy to every 15 minutes and rebooted the sql server. We will see today if it helped.

  • September 14, 2009 at 6:12 am

    #1053123

    by the way, thanks for the responses.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply