Timeout setting for SQL Server
Solution 1
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;
}
Solution 2
Yes, there are 2 kinds of timeout that can be set
Both default to 30 seconds in VBA, .net etc
Related videos on Youtube
George2
Updated on July 09, 2022Comments
-
George2 almost 2 years
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,
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?)?
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); } }
-
George2 over 14 yearsThanks gbn! What kinds of exception will be thrown or in other words what kinds of exception we should catch in our code to handle timeout issue?
-
George2 over 14 yearsThanks Marc, what kinds of exception will be thrown or in other words what kinds of exception we should catch in our code to handle timeout issue?
-
George2 over 14 yearsThanks Philip, do you mean besides connection timeout and command timeout, there is a 3rd type of timeout called Transaction timeout?
-
ZygD over 14 yearsIn SSMS, I get "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." YOu can test using WAITFOR DELAY '00:00:40' to force a 40 second wait and exception for Command timeout. For connection timeout, you can make up a server name and try to connect.
-
Philip Fourie over 14 years@George2, yes that is correct. It an obscure one that caught us in production. Here is some information on it. social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/…
-
George2 over 14 yearsThanks, for command timeout, what kinds of exception should be caught?
-
George2 over 14 yearsThanks for command time out, commit timeout and connection timeout, what kinds of exception should we catch?
-
marc_s over 14 yearsFor connection, you would typically see SqlException (if something in general does wrong), SecurityException (if permissions are missing in a partial-trust environment), or an ArgumentException, if the connection string is invalid. The SqlCommand has the SqlException as the general exception, plus InvalidArgument or SecurityEXceptions as well, depending on the methods you call - see the MSDN docs for details which exception is thrown by which methods (too many to list here)
-
user over 7 yearsmsdn.microsoft.com/en-us/library/… has some useful example code for triggering a command timeout exception.