Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no
来源:互联网 发布:数据库管理系统功能 编辑:程序博客网 时间:2024/05/21 10:24
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.
The article is referenced from: http://blogs.msdn.com/b/spike/archive/2008/07/31/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding.aspx
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no
- Timeout expired , The timeout period elapsed prior to completion of the operation or the server
- Timeout expired , The timeout period elapsed prior to completion of the operation or the server
- Timeout expired , The timeout period elapsed prior to completion of the operation or the server
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server...
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- Error: timeout expired. the timeout period elapsed prior to completion of the operation or the server is not responding (Microso
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is n
- .Net 连接池的配置Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”
- mysql Timeout expired. The timeout period elapsed prior to completio
- Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This ma
- Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
- timeout expired.then timeout period elapsed prior to obtaining a connection from the pool.……
- error code 121:The semaphore timeout period has expired.
- 关于"No result defined for action com.ccxe.credit.action.ReportAction and result input"的错误
- 渐进符号(二)
- 配置自己的CocoaPods库
- 20个经典bootsrtap后台html网站模板推荐
- LeetCode – Search Insert Position
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no
- 神奇的魔术方阵
- 提示microsoft incremental linker已停止工作解决方法
- CentOS6 rpm 安装Open vSwitch(OVS)报错
- 说说spring的自动装配
- Sqlite3支持的数据类型 日期函数 Sqlite3 函数
- Hdu 2082 找单词 母函数
- strcmp,strncmp,memcmp,strcat,strncat函数实现
- 《C++ Primer》读后感