.Net 2.0 和 SQL Server 2008 挂起进程
我创建了一个简单的 .Net Web 服务，它运行一个非常简单的查询（通常应该非常快）。 查询如下：
SELECT v.email
FROM dbo.Reporting r
INNER JOIN dbo.Reporting_EmailList el ON r.reportID = el.reportID
INNER JOIN OtherDB.dbo.V_ActiveDir v ON el.userGUID = v.objectGUID
WHERE r.reportID = @reportID
V_ActiveDir 是活动目录数据的视图。 这个查询本质上是让我获得一个电子邮件地址列表，报告应该通过电子邮件发送到这些地址。 我的报告表目前只有 3 条记录。 我试图将字段添加到报告表中，但花了很长时间，这就是我发现该查询挂在 SQL 进程中的原因。 当天早些时候，该脚本生成了以下错误：
Timeout expired. The timeout period elapsed prior to completion
of the operation or the server is not responding.
...这解释了我认为的挂起过程。 我们试图杀死它，但现在它处于回滚状态，我认为我们可能必须重新启动 sql 服务器（在我看来，选择查询会尝试回滚是很奇怪的）。 sql server 或 .net 有什么办法可以防止这种情况再次发生吗？ 或者我可以在 sql 中删除这个进程...是否有可能有东西挂在线程池中？ 我仍然可以从报告表中进行选择，因此没有表锁，只有当我尝试更改该表时，它才会旋转。
我对 IIS 没有太多经验，因为我无法访问它，但如果有建议我可以传递。
编辑：这可能是因为在我的 catch 语句中我没有检查 SqlConnection 是否打开以及是否关闭？
I created a simple .Net web service which runs a very simple query (which should normally be very quick). The query is something like this:
SELECT v.email
FROM dbo.Reporting r
INNER JOIN dbo.Reporting_EmailList el ON r.reportID = el.reportID
INNER JOIN OtherDB.dbo.V_ActiveDir v ON el.userGUID = v.objectGUID
WHERE r.reportID = @reportID
V_ActiveDir is a view for active directory data. This query essentially gets me a list of email addresses in which the report should be emailed out to. My Reporting table currently only has 3 records. I was trying to add fields to the Reporting table but it was taking forever, which is how I found out that this query was hanging in a sql process. Earlier in the day this script generated the following error:
Timeout expired. The timeout period elapsed prior to completion
of the operation or the server is not responding.
... which explains the hanging process I think. We tried to kill it but now its in a rollback state and I think we may have to reboot sql server (which in my mind is weird that a select query would try to rollback). Is there any way with either sql server or .net to prevent this from occuring again? Or a way I can remove this process in sql... is it possible something is hanging in the thread pool? I can still select from the Reporting table so theres no table lock, only when I try to alter this table does it just spin its wheels.
I don't have much experience with IIS as I don't have access to it, but if there is a suggestions I can pass it along.
Edit: could this be cause because in my catch statement I don't check to see if the SqlConnection is open, and if it is close it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，如果 SQL 处于恢复状态（例如上面描述的回滚），切勿重新启动 SQL。
ASP.NET 查询通常会在 30 或 60 秒后超时。
检查以下字段是否已编制索引：
OtherDB 位于何处？ 它是链接数据库吗？ 这可能是问题的一部分。
该查询的查询计划是什么样的？ 你能看到那里有什么可疑的地方吗？
First off, NEVER reboot SQL if it is in a recovery state (e.g. the rollback you describe above).
ASP.NET queries normally time out at 30 or 60 seconds.
Check if the following fields are indexed:
Where is the OtherDB located? Is it a linked database? This could be part of the problem.
What does the query plan look like for this query? Can you see anything suspect there?