how do I fix SqlException Time-out occurred while waiting for buffer latch type 2 for page (1:37660679), database ID 10

Question

I was running an application for a few hours and then suddenly:

SqlException was unhandled by user code:

Time-out occurred while waiting for buffer latch type 2 for page (1:37660679), database ID 10.

Viewing details of the exception shows it is a "Number" "845". ErrorCode and 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.

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):

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.

There are lots of 847 that look about the same. Then there are much fewer with event-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.

Here is an xml view of an 847 event:

- <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.

Answer 1

The FGCB_ADD_REMOVE latch probably means in your case the a file was expanded due to auto-grow. This causes lots of waiting and IO load.

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.

Determine if the server disks are overloaded (for example using perfmon) and improve the situation.