Post reply

How to resolve SQL Client Timeouts?

  • September 25, 2015 at 11:47 am

    #320867

    Hello, I am a .NET programmer who also works with SQL 2008 R2 Server. Sometimes I get Client timeout errors from TSQL queries and BCP commands.

    When I contact the company DBA, he looks at the SQL logs, and reports to me that he does not find any problems.

    So... I am getting client side timeout exceptions, but there is nothing wrong ?

    I am ok with this being a "client" problem, but how does one who is not a DBA resolve the problem?

    I would welcome any suggestions on how to diagnose/resolve timeout errors.

    Thanks

    Tom

  • September 25, 2015 at 11:49 am

    #1829595

    Are these connection timeouts or query timeouts?

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • September 25, 2015 at 11:55 am

    #1829602

    Here is one example:

    I am doing a BCP command from .NET Process.Start.

    Sometimes I get this error:

    TCP Provided: Timeout error

    Sometime I get this error:

    Named Pipes: Timeout error

    Thanks

  • September 25, 2015 at 11:59 am

    #1829605

    A timeout is the client saying it's waited too long. Your DBA is right, there will be nothing in the SQL error logs because as far as SQL's concerned, the client just got bored and went away.

    You can either increase the timeout in the application (the default is 30 seconds for .net), or take whatever's timing out to the DBA and ask him nicely to help you optimise the process.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 25, 2015 at 12:14 pm

    #1829612

    How long has the command been running when the timeout occurs?

    In your client, have you set connection timeout and command timeout to 0 or some reasonably long duration?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • September 25, 2015 at 12:16 pm

    #1829613

    Hi Gail,

    Thanks for taking time to reply.

    I have the timeout set to 60 seconds.

    What is frustrating is that is works most of the time.

    I will take your suggestions to increase the timeout, and contact my DBA for help.

    Thank you,

    Tom

  • September 25, 2015 at 12:25 pm

    #1829619

    There's about 16 million reasons why something could timeout sometimes and not others. Put some extra logging into your app to record exactly what was being done, then once you know exactly what query/process is getting the timeouts, work on optimising it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 25, 2015 at 12:33 pm

    #1829622

    null

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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