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.

Answer 2

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.

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.

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.