C# 如何修复 SqlException 在等待页面的缓冲区闩锁类型 2 时发生超时 (1:37660679)，数据库 ID 10
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15055815/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how do I fix SqlException Time-out occurred while waiting for buffer latch type 2 for page (1:37660679), database ID 10
提问by tomsv
I was running an application for a few hours and then suddenly:
我运行了几个小时的应用程序，然后突然：
SqlException was unhandled by user code:
用户代码未处理 SqlException：
Time-out occurred while waiting for buffer latch type 2 for page (1:37660679), database ID 10.
等待页面 (1:37660679) 的缓冲区闩锁类型 2 时发生超时，数据库 ID 为 10。
Viewing details of the exception shows it is a "Number" "845". ErrorCode and HRESULT -2146232060
查看异常的详细信息显示它是一个“数字”“845”。错误代码和 HRESULT -2146232060
Question: How do I fix this or debug this problem?
问题：如何解决或调试此问题？
I am running ASP.NET C# .NET 4.5 and SQL Server 2012.
I ran chkdsk but did not find any errors.
我正在运行 ASP.NET C# .NET 4.5 和 SQL Server 2012。
我运行了 chkdsk 但没有发现任何错误。
There is nothing about an 845 event in the log. Here is some of what I found in the application log(For an event-id 847):
日志中没有关于 845 事件的任何内容。以下是我在应用程序日志中发现的一些内容（对于事件 ID 847）：
Timeout occurred while waiting for latch: class 'FGCB_ADD_REMOVE', id 00000004F146FBD8, type 2, Task 0x00000004F60450C8 : 0, waittime 300 seconds, flags 0x1a, owning task 0x00000004EDC38928. Continuing to wait.
等待闩锁时发生超时：类“FGCB_ADD_REMOVE”，ID 00000004F146FBD8，类型 2，任务 0x00000004F60450C8：0，等待时间 300 秒，标志 0x1a，拥有任务 0x028003000C 继续等待。
There are lots of 847 that look about the same. Then there are much fewer with event-id 846:
有很多 847 看起来都差不多。然后事件 ID 为 846 的要少得多：
A time-out occurred while waiting for buffer latch -- type 2, bp 00000004F96EE880, page 1:37660679, stat 0x10b, database id: 10, allocation unit Id: 72057594048544768, task 0x00000004D502E188 : 0, waittime 300 seconds, flags 0x1a, owning task 0x00000004D5316558. Not continuing to wait.
等待缓冲区闩锁时发生超时 - 类型 2，bp 00000004F96EE880，第 1 页：37660679，统计信息 0x10b，数据库 ID：10，分配单元 ID：72057594048544768，任务 0x00850020x0085002 秒，0x00850000x0085002任务 0x00000004D5316558。没有继续等待。
Here is an xml view of an 847 event:
这是 847 事件的 xml 视图：
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="16384">847</EventID>
<Level>4</Level>
<Task>2</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2013-02-24T19:21:54.000000000Z" />
<EventRecordID>281870</EventRecordID>
<Channel>Application</Channel>
<Computer>xyz-PC</Computer>
<Security UserID="S-1-and-so-on" />
</System>
- <EventData>
<Data>FGCB_ADD_REMOVE</Data>
<Data>00000004F146FBD8</Data>
<Data>2</Data>
<Data>00000004D5316188</Data>
<Data>0</Data>
<Data>1200</Data>
<Data>1a</Data>
<Data>00000004EDC38928</Data>
<Binary>4F0300000A00000006000000540053002D005000430000000800000053006300680061006200650072000000</Binary>
</EventData>
</Event>
The error occurred on a line
错误发生在一行上
db.SubmitChanges();
In the watch window I can see:
在监视窗口中，我可以看到：
db.GetChangeSet() {Inserts: 1, Deletes: 0, Updates: 0} System.Data.Linq.ChangeSet
Googling shows up some hotfixes from microsoft but they are only for SQL server 2008.
谷歌搜索显示了微软的一些修补程序，但它们仅适用于 SQL Server 2008。
采纳答案by usr
The FGCB_ADD_REMOVElatch probably means in your case the a file was expanded due to auto-grow. This causes lots of waiting and IO load.
在FGCB_ADD_REMOVE你的情况可能是锁的手段，由于自动增长的一个文件被扩大。这会导致大量等待和 IO 负载。
Or maybe this is just the symptom of a generally IO-overloaded server (either due to load or due to failing disks). Yeah it is probably only a symptom because you are seeing other IO waits timing out, too.
或者这可能只是一个通常 IO 过载的服务器的症状（由于负载或由于磁盘故障）。是的，这可能只是一种症状，因为您也看到其他 IO 等待超时。
Determine if the server disks are overloaded (for example using perfmon) and improve the situation.
确定服务器磁盘是否过载（例如使用 perfmon）并改善这种情况。
回答by Brian Towers
I would suggest checking / changing the autogrowth settings for the database affected and possibly tempdb as well. Right click, properties, files to get there. Then if you just have the default settings (autogrowth by 1mb on data, 10% on log) change those to something like 10mb and 10mb. You might want to do something similar with tempdb as well. In general set these to values which suit the size and use of your database. Over time you will get a better idea of this than me.
我建议检查/更改受影响的数据库和可能的 tempdb 的自动增长设置。右键单击，属性，文件到那里。然后，如果您只有默认设置（数据自动增长 1mb，日志自动增长 10%），请将它们更改为 10mb 和 10mb。您可能还想对 tempdb 执行类似的操作。通常将这些设置为适合您的数据库大小和用途的值。随着时间的推移，你会比我更清楚这一点。
If you know the "right size" your database should be then set that size in the file properties page directly and preempt any related file expansion errors. What I mean by this is that if, for example, your db is currently 10GB and you anticipate in the next few weeks that you will be adding another 3 or 4 GB of data to it then just set the initial size to something like 15GB. Don't forget that indexes take space too when estimating likely file growth.
如果您知道“正确的大小”，您的数据库应该直接在文件属性页面中设置该大小并抢占任何相关的文件扩展错误。我的意思是，例如，如果您的数据库当前为 10GB，并且您预计在接下来的几周内您将再添加 3 或 4 GB 的数据，那么只需将初始大小设置为 15GB。不要忘记在估计可能的文件增长时索引也会占用空间。
Some more general "good housekeeping" ideas -
一些更一般的“良好的内务管理”想法 -
Make sure you are defragmenting regularly.
确保定期进行碎片整理。
Think about several data files according to number of CPUs. Put data files and log files on different disk if possible.
根据 CPU 的数量考虑几个数据文件。如果可能，将数据文件和日志文件放在不同的磁盘上。
