Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Timeout setting for SQL Server

I am using VSTS 2008 + ADO.Net + C# + .Net 3.5 + SQL Server 2008. I am using ADO.Net at client side to connect to database server to execute a store procedure, then return result from the store procedure.

Here is my code. I have two issues about timeout,

  1. If I do not explicitly set any timeout related settings, for the connection to database server, are there any timeout settings (e.g. if can not connect to database server for some default amount of time, there will be some timeout exception?)?

  2. If I do not explicitly set any timeout related settings, for the execution of the store procedure, are there any timeout settings (e.g. if can not retrieve results from server to ADO.Net client for some default amount of time, there will be some timeout exception?)?

        using (SqlConnection currentConnection = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Trusted_Connection=true;Asynchronous Processing=true"))     {         // check current batch conut         currentConnection.Open();         using (SqlCommand RetrieveOrderCommand = new SqlCommand())         {             RetrieveOrderCommand.Connection = currentConnection;             RetrieveOrderCommand.CommandType = CommandType.StoredProcedure;             RetrieveOrderCommand.CommandText = "prc_GetOrders";             RetrieveBatchCountCommand.Parameters.Add("@Count", SqlDbType.Int).Direction = ParameterDirection.Output;             RetrieveBatchCountCommand.ExecuteNonQuery();             int rowCount = Convert.ToInt32(RetrieveOrderCommand.Parameters["@Count"].Value);         }     } 
like image 794
George2 Avatar asked Aug 30 '09 16:08

George2


People also ask

What is the default SQL Server timeout?

The server default timeout for SQL server is 600 seconds.

How do I fix timeout error in SQL Server?

If you encounter a connection-timeout error, follow the steps: Increase the connection-timeout parameter. If you use an application to connect to SQL Server, increase the relevant connection-timeout parameter values and check whether the connection eventually succeeds.

What is session timeout in SQL Server?

You can place a limit on the amount of time a SQL database session is allowed to be idle before the system terminates it. You can impose timeouts on both individual users and groups. In addition, users can set their own timeouts.


2 Answers

As gbn already mentioned, 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, your 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;     } 
like image 168
marc_s Avatar answered Oct 02 '22 13:10

marc_s


Yes, there are 2 kinds of timeout that can be set

  1. Connection timeout
  2. Command timeout

Both default to 30 seconds in VBA, .net etc

like image 39
gbn Avatar answered Oct 02 '22 13:10

gbn