locked
C# SqlServer connection timeout problem RRS feed

  • Question

  • hello,i have a problem ,when i connect to a sqlserver database,if the machine has closed,or the machine have not a sqlserver service,my progrem will block about 30s,it's too long,so i change the connection string like that
    SqlConnection con = new SqlConnection("server=192.168.1.28;uid=sa;pwd=itneste;database=FileTransport;Connection Timeout=5;");
    

    and i set the command timeout =5,but not effect......
    what's the problem,and how do?
    thanks!
    Wednesday, October 27, 2010 6:19 AM

Answers

  • Hello,i have a problem ,when i connect to a sqlserver database,if the machine has closed,or the machine have not a sqlserver service,my progrem will block about 30s,it's too long,so i change the connection string like that

    SqlConnection con = new SqlConnection("server=192.168.1.28;uid=sa;pwd=itneste;database=FileTransport;Connection Timeout=5;");
    



    and i set the command timeout =5,but not effect......

    Connection timeout does not work if you specify 5 seconds it will not timeout in 5 seconds. This is by design...

    I can confirm that it took for me about 42.3 seconds before I get timeout exception and the reason for this is timeout counter does not start until network connectivity has been established..It is a low level TCP/IP protocol related stuff.

    Here is the posting that confirms

    /********************
    Be aware that the Connection Timeout value is "at least" x instead of "at
    most" x seconds. We will not automatically abort the connection attempt when
    the timeout is reached. Think of a loop:

    Try to connect loop:
    If time is greater than Connection Timeout exit.
    call win32 api; //this can take some time.
    End try to connect loop.

    In this scenario your worst case connection timeout will be Connection
    Timeout -1 + time it takes to do win32 api call. In some cases, like a
    server that is down this can add up to a significant chunk of time.

    --
    Angel Saenz-Badillos [MS] Managed Providers
    This posting is provided "AS IS", with no warranties, and confers no
    rights.
    /****************************

    Wednesday, October 27, 2010 1:47 PM

All replies

  • Hello,i have a problem ,when i connect to a sqlserver database,if the machine has closed,or the machine have not a sqlserver service,my progrem will block about 30s,it's too long,so i change the connection string like that

    SqlConnection con = new SqlConnection("server=192.168.1.28;uid=sa;pwd=itneste;database=FileTransport;Connection Timeout=5;");
    



    and i set the command timeout =5,but not effect......

    Connection timeout does not work if you specify 5 seconds it will not timeout in 5 seconds. This is by design...

    I can confirm that it took for me about 42.3 seconds before I get timeout exception and the reason for this is timeout counter does not start until network connectivity has been established..It is a low level TCP/IP protocol related stuff.

    Here is the posting that confirms

    /********************
    Be aware that the Connection Timeout value is "at least" x instead of "at
    most" x seconds. We will not automatically abort the connection attempt when
    the timeout is reached. Think of a loop:

    Try to connect loop:
    If time is greater than Connection Timeout exit.
    call win32 api; //this can take some time.
    End try to connect loop.

    In this scenario your worst case connection timeout will be Connection
    Timeout -1 + time it takes to do win32 api call. In some cases, like a
    server that is down this can add up to a significant chunk of time.

    --
    Angel Saenz-Badillos [MS] Managed Providers
    This posting is provided "AS IS", with no warranties, and confers no
    rights.
    /****************************

    Wednesday, October 27, 2010 1:47 PM
  • You could use a background thread to make the connection so the main thread is not blocked waiting for the timeout.
    elsasoft.org
    Wednesday, October 27, 2010 1:54 PM