locked
Hai all. RRS feed

  • Question

  • how can we troubleshoot connection timeout issues in sql server,like when we try to login to sql server sometimes it do occur that " connection timeout error"

    how can we troubleshoot and possible resolution for this?

    thanks in advance,

    Friday, July 12, 2013 11:34 AM

Answers

  • Hi,

    Please go through the below link..

    Follow me...

    Click me

    Hope it helps,

    Thanks,

    Rohit 


    • Edited by Rohit Kolekar Friday, July 12, 2013 11:45 AM Modify
    • Marked as answer by Fanny Liu Friday, July 19, 2013 10:29 AM
    Friday, July 12, 2013 11:41 AM
  • There are two types of timeouts:

    1) Connection Timeout: this is controlled by your connection string:

    Data Source=.;Initial Catalog=TestDB;
       Trusted_Connection=true;Asynchronous Processing=true

    If you add a Connect Timeout=120 to this string, you're connection will try for 120 seconds to get opened and then aborts.

    Data Source=.;Initial Catalog=TestDB;
       Trusted_Connection=true;Asynchronous Processing=true;
       Connect Timeout=120;

    2) Command timeout: for each command, you can also specify a timeout - ADO.NET will wait for that amount of time before cancelling out your query. You specify that on the SqlCommand object:

        using (SqlCommand RetrieveOrderCommand = new SqlCommand())
        {
           RetrieveOrderCommand.CommandTimeout = 150;
        }

    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you.

    • Marked as answer by Fanny Liu Friday, July 19, 2013 10:29 AM
    Friday, July 12, 2013 11:50 AM

All replies

  • Hi,

    Please go through the below link..

    Follow me...

    Click me

    Hope it helps,

    Thanks,

    Rohit 


    • Edited by Rohit Kolekar Friday, July 12, 2013 11:45 AM Modify
    • Marked as answer by Fanny Liu Friday, July 19, 2013 10:29 AM
    Friday, July 12, 2013 11:41 AM
  • There are two types of timeouts:

    1) Connection Timeout: this is controlled by your connection string:

    Data Source=.;Initial Catalog=TestDB;
       Trusted_Connection=true;Asynchronous Processing=true

    If you add a Connect Timeout=120 to this string, you're connection will try for 120 seconds to get opened and then aborts.

    Data Source=.;Initial Catalog=TestDB;
       Trusted_Connection=true;Asynchronous Processing=true;
       Connect Timeout=120;

    2) Command timeout: for each command, you can also specify a timeout - ADO.NET will wait for that amount of time before cancelling out your query. You specify that on the SqlCommand object:

        using (SqlCommand RetrieveOrderCommand = new SqlCommand())
        {
           RetrieveOrderCommand.CommandTimeout = 150;
        }

    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you.

    • Marked as answer by Fanny Liu Friday, July 19, 2013 10:29 AM
    Friday, July 12, 2013 11:50 AM
  • Time out can occur due to multiple reasons i.e. due to network, blocking, deadlocks,  I/O or due to memory dumps.

    Check the SQL server and event viwer logs, it will definitely provide the root cause.

     Also, you can run the profiler during the timeout and check at which stage timeout occurred

     If possible, please share some more details like when actually you are facing this issue?

    Is any active processes found on the server during the timeout?


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, July 12, 2013 12:45 PM