blocks|key|117182|text|我们能确保SQLConnection工作正常吗？可能的情况是，SQL登录条件发生了更改，并且连接超时。如果你在这里发布错误信息，可能会更有帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|117183|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Can we make sure that the SQLConnection is working fine. It could be the case that SQL login criteria is changed and connection is getting a timeout. It will be probably more helpful if you post the error message here.

blocks|key|117195|text|是否有可能将“引号”作为传递给查询的字符串的一部分？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|117196|在特殊条件不再起作用的情况下，是否存在与日期相关的查询？|117197|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

any chances of a "quotes" as part of the strings you are passing to queries?

any chances of date dependent queries where a special condition is not working anymore?

blocks|key|2571091|text|我建议您尝试更新的某个记录上存在锁定，或者存在尚未完成的事务。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2571092|我知道这不是您的问题的一部分，但是在看到您的示例代码后，我必须做出这样的评论:您是否有可能更改在数据库上执行sql的方法？这在很多层面上都很糟糕。|2571093|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I would suggest that there is a lock on one of the records that you are trying to update, or there are transactions that haven't been completed.

I know this is not part of your question, but after seeing your sample code i have to make this comment: is there any chance you could change your method of executing sql on your database? It is bad on so many levels.

blocks|key|2571095|text|我将陈述一个显而易见的事实--有些事情发生了变化。这可能是一次升级没有达到预期的效果--可能是网络组件出了问题--可能是一个闪存盘--可能是很多东西--但访问路径中的某些东西已经发生了变化。您还看到了哪些其他问题指示，包括与此应用程序不直接相关的问题？数据库存储在哪里(本地磁盘，网络存储盒，天使写在针头上，其他)？您的系统管理员是否以某种方式“帮助”或“改进”了事情？代码并没有过时--发生了一些其他事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2571096|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I'll state the obvious - something changed. It could be an upgrade that isn't having the desired effect - it could be a network component going south - it could be a flakey disk - it could be many things - but something in the access path has changed. What other problem indications are you seeing, including problems not directly related to this application? Where is the database stored (local disk, network storage box, written by angels on the head of a pin, other)? Has your system administrator "helped" or "improved" things somehow? The code has not worn out - something else has happened.

blocks|key|3896634|text|有没有可能这个查询随着时间的推移变得越来越慢，现在刚刚超过了默认超时时间？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3896635|acc表中有多少条记录，AccNo和AccSeq上有索引吗？另外，您使用的是什么版本的SQL？|3896636|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Is it possible that this query has been getting slower over time and is now just exceeded the default timeout?

How many records would be in the acc table and are there indexes on AccNo and AccSeq?
Also what version of SQL are you using?

blocks|key|3896649|text|您可以将更新重写为单个查询。这将比原始查询运行得快得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3896650|UPDATE+subquery
SET+AccNo+=+NewAccNo,+AccSeq+=+NewAccSeq
FROM
++++(SELECT+AccNo,+AccSeq,+
++++++++DENSE_RANK()+OVER+(PARTITION+BY+AccNo+ORDER+BY+AccNo)+NewAccNo,
++++++++ROW_NUMBER()+OVER+(PARTITION+BY+AccNo+ORDER+BY+AccDate,+AccSeq)
++++++++++++%2B+10000+NewAccSeq
+++++FROM+Acc)+subquery|code-block|syntax|javascript|3896651|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can rewrite the update as a single query. This will run much faster than the original query.

<pre><code>UPDATE subquery
SET AccNo = NewAccNo, AccSeq = NewAccSeq
FROM
 (SELECT AccNo, AccSeq, 
 DENSE_RANK() OVER (PARTITION BY AccNo ORDER BY AccNo) NewAccNo,
 ROW_NUMBER() OVER (PARTITION BY AccNo ORDER BY AccDate, AccSeq)
 + 10000 NewAccSeq
 FROM Acc) subquery
</code></pre>

blocks|key|117305|text|您多久没有更新统计数据和重建索引了？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|117306|您的数据增长了多少？对于小的数据集工作良好的查询可能对大的数据集不好。|117307|你有没有遇到锁定问题？AMJ您是否检查过活动监视器，以查看在超时发生时是否存在锁定？|117308|您是否运行过profiler来获取超时的查询，然后直接在服务器上运行它？那是不是更快了？也可能是将信息从数据库服务器移动到应用程序时出现的网络问题。这至少可以告诉您是SQl服务器问题还是网络问题。|117309|正如Bob+Jarvis所说，服务器最近发生了什么变化？数据库结构本身是否发生了变化？是不是有人加了触发器？|117310|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

How long since you updated statistics and rebuilt indexes?

How much has your data grown? Queries that work fine for small datasets can be bad for large ones.

Are you getting locking issues? [AMJ] Have you checked activity monitor to see if there are locks when the timeout occurs?

Have you run profiler to grab the query that is timing out and then run it directly onthe server? Is it faster then? Could also be network issues in moving the information from the database server to the application. That would at least tell you if it s SQl Server issue or a network issue.

And like Bob Jarvis said, what has recently changed on the server? Has something changed in the database structure itself? Has someone added a trigger?

blocks|key|659817|text|也许您应该将CommandTimeout属性设置为更高的延迟？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|659818|这样做将允许您的命令等待底层数据库响应的时间稍长一些。在我看来，在创建另一个命令来执行update之前，您可能没有让数据库引擎有足够的时间来执行所有必需的操作。|659819|知道在给内存中的对象提供数据的同时，SqlDataReader会继续"SELECT"。然后，在阅读时，您需要您的代码更新一些其他表，这些表在SqlCommand要求的时间超时之前是DBE无法处理的。|659820|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx|1|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx|2|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx^0|6|E|6|E|0|0|17|6|0|I|D|Y|8|1Y|A|I|D|1|1Y|A|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@$9|1B|A|1C|1|1D]|$9|1E|A|1F|1|1G]]|E|$]]|$1|J|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|S]]|T|$5|M|N|O|E|$P|U]]]]

Perhaps should you set the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx" rel="nofollow noreferrer"><code>CommandTimeout</code></a> property to a higher delay?

Doing so will allow your command to wait a little longer for the underlying database to respond. As I see it, perhaps are you not letting time enough for your database engine to perform all what is required before creating another command to perform your <code>update</code>.

Know that the <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx" rel="nofollow noreferrer"><code>SqlDataReader</code></a> continues to <code>"SELECT"</code> while feeding the in-memory objects. Then, while reading, you require your code to update some other table, which your DBE just can't handle, by the time your <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx" rel="nofollow noreferrer"><code>SqlCommand</code></a> requires, than times out.

blocks|key|117370|text|在HLGEM的建议之后，我会检查数据并确保它是正确的。在这种情况下，95%25的时间是数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|117371|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

After HLGEM's suggestions, I would check the data and make sure it is okay. In cases like this, 95% of the time it is the data.

blocks|key|117403|text|确保磁盘已进行碎片整理。是的，我知道，但这确实是有区别的。而不是内置的碎片整理程序。一个像PerfectDisk一样进行碎片整理和优化的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|117404|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Make sure disk is defragged. Yes, I know, but it does make a difference. Not the built-in defragger. One that defrags and optimizes like PerfectDisk.

blocks|key|117453|text|你测试过显而易见的东西了吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|117454|您是否直接在SQL+Server+Management+Studio上运行了"update+Acc+Set+AccNo=@NewAccNo，AccSeq=@NewAccSeq+where+AccNo=@AccNo+AccSeq=@AccSeq“查询？(请将变量替换为一些硬编码的值)|117455|您是否在另一位同事的PC上运行过相同的测试？|117456|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Have you tested the obvious? 

Have you run the "update Acc Set AccNo=@NewAccNo,AccSeq=@NewAccSeq where AccNo=@AccNo and AccSeq=@AccSeq" query directly on your SQL Server Management Studio? (Please replace the variables with some hard coded values)

Have you run the same test on another colleague's PC?

blocks|key|3896833|text|这可能有点冒险，但是如果您的整个应用程序已经停止工作，您是否已经用完了数据库中事务日志的空间？它可能已被指定为绝对大小，并且已达到该大小，或者您的磁盘已满。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3896834|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

This may be a bit of a long shot, but if your entire application has stopped working, have you run out of space for the transaction log in your database? Either it's been specified to an absolute size, and that has been reached, or your disk is just full.

blocks|key|3896869|text|可能您的表包含了更多信息，并且在配置文件中定义了SqlConnection.ConnectionTimeout属性值，但值很小。并且这个值不是执行查询所必需的。您可以尝试优化查询，也可以重建索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3896870|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx^0|O|V|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

May be your tables include more information, and defined <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx" rel="nofollow noreferrer">SqlConnection.ConnectionTimeout</a> property value in config file with little value. And this value isn't necessary to execute your queries.
you can trying optimize your queries, and also rebuilt indexes.

We got a legacy vb.net applicaction that was working for years 
But all of a sudden it stops working yesterday and gives sql server timeout
Most part of application gives time out error , one part for example is below code :

<pre><code>command2 = New SqlCommand("select * from Acc order by AccDate,AccNo,AccSeq", SBSConnection2)
 reader2 = command2.ExecuteReader()
 If reader2.HasRows() Then
 While reader2.Read()
 If IndiAccNo &lt;&gt; reader2("AccNo") Then
 CAccNo = CAccNo + 1
 CAccSeq = 10001
 IndiAccNo = reader2("AccNo")
 Else
 CAccSeq = CAccSeq + 1
 End If
 command3 = New SqlCommand("update Acc Set AccNo=@NewAccNo,AccSeq=@NewAccSeq where AccNo=@AccNo and AccSeq=@AccSeq", SBSConnection3)
 command3.Parameters.Add("@AccNo", SqlDbType.Int).Value = reader2("AccNo")
 command3.Parameters.Add("@AccSeq", SqlDbType.Int).Value = reader2("AccSeq")
 command3.Parameters.Add("@NewAccNo", SqlDbType.Int).Value = CAccNo
 command3.Parameters.Add("@NewAccSeq", SqlDbType.Int).Value = CAccSeq

 command3.ExecuteNonQuery()
 End While
End If
</code></pre>

It was working and now gives time out in command3.ExecuteNonQuery()
Any ideas ?

~~~~~~~~~~~
Some information :

There isnt anything that has been changed on network and the app uses local database
The main issue is that even in development environment it donest work anymore

All of a Sudden , Sql Server Timeout

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我们得到了一个运行了数年的遗留vb.net应用程序，但它昨天突然停止工作，并导致sql server超时。大部分应用程序都会出现超时错误，例如，其中一部分代码如下：command2 = New SqlCommand("select * from Acc order by AccDate,AccNo,AccSeq", S...

问突然，Sql Server超时
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问突然，Sql Server超时EN