The Scenario:
Sometimes you may get a timeout issue looking something like this:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
The important part here is what is in the exception message:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This may give you the impression that the server is down or something similar.
However, this is basically the SqlCommand.CommandTimeout property that has expired; the default timeout is 30 seconds.
See more at:
".NET Framework Class Library -> SqlCommand.CommandTimeout Property"
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
Now, why would it time out?
There are 2 common reasons.
Long running tasks or uncommitted transactions. Let's show this by example.
In the first example, we emulate that the command execution takes a very long time to execute and return, for example there could be millions of rows being updated or for some other reason the execution takes a long time.
In the code I just call the SQL Server method "waitfor delay" that will pause the execution in SQL Server for 30 seconds, I then change the SqlCommand.CommandTimeout from 30 seconds to 10 seconds so that we do not have to sit all day and wait for the exception. The code should be pretty self explanatory, just create a console application in Visual Studio.
Note that we connect to the trusty Northwind, if we would set the CommandTimeout to 60 seconds, then after 30 seconds we would get our Shippers table data back.
static void Main(string[] args)
{
string cString = @"Data source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
using (SqlConnection sc = new SqlConnection(cString))
{
try
{
SqlCommand cmd = new SqlCommand("waitfor delay '00:00:30';select * from Shippers", sc);
cmd.CommandTimeout = 10;
Console.WriteLine("CommandTimeout: {0}", cmd.CommandTimeout);
sc.Open();
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
Console.WriteLine("{0} : {1}", r[0].ToString(), r[1].ToString());
sc.Close();
}
catch (SqlException se)
{
Console.WriteLine(se);
}
}
}
Run it, and after 10 seconds you will get the exception:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
...
For the second reason, that there might be an uncommitted transaction, again we will use Northwind running the following the code, almost the same as above (only difference is that there is no call to “waitfor delay” in the SQL):
static void Main(string[] args)
{
string cString = @"Data source=<your server>;Integrated Security=SSPI;Initial Catalog=Northwind";
using (SqlConnection sc = new SqlConnection(cString))
{
try
{
SqlCommand cmd = new SqlCommand("select * from Shippers", sc);
cmd.CommandTimeout = 10;
Console.WriteLine("CommandTimeout: {0}", cmd.CommandTimeout);
sc.Open();
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
Console.WriteLine("{0} : {1}", r[0].ToString(), r[1].ToString());
sc.Close();
}
catch (SqlException se)
{
Console.WriteLine(se);
}
}
}
Run this code and you should get the rows in the Shippers table returned.
Now, open Query Analyzer or Sql Server Management Studio and execute an uncommitted transaction on the Shippers table, like so:
use Northwind
go
begin tran
update Shippers set CompanyName = 'aaaaa' where ShipperID = 1
--commit
(Note that the new value, in this case 'aaaaa' must be different compared to the existing one in order to see the problem)
Rerun the code above, and after 10 seconds you will, again, get the exception:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
...
Go back to Query Analyzer or Sql Server Management Studio and commit the transaction, rerun code, and you will once again get the Shippers table data returned.
So to summarize, if the command you are executing is a long running one, adjust the CommandTimeout accordingly. If there are uncommitted transactions, you need to find what and where they are and change your code or your stored procedures accordingly.This is outside the scope of this blog, but one way to check for uncommitted transactions is to from QA or SSMS run the following:
dbcc opentran ('Northwind')
This will show if there are any blocked spids in the Northwind database which could be an indication of uncommitted transactions and queries that are blocked as a result of this.
It may seem obvious that a command times out if the command timeout expires. The background for this post is that I had a case where occasionally my customers’ users could not log in to their system. There was no clear pattern to this, and what happened when they tried to log in was that they got the exception above. In the end it turned out that they had a page in the application that allowed the user to change employee information.
The problem was that when they made the change, they opened a transaction, however, they did not commit it until the user pressed a Save button. The interval between starting the edit of the employee information and the saving of it could be anything, either they made the change and saved immediately, or they made the change and went to lunch without saving.
During this time, all the logins would fail since the login functionality basically did a select from the employee table with the users’ login and password, which subsequently failed since the table was locked by the uncommitted transaction.
Once this was figured out, and the changes were made, all was well in login land.