A time-out occurred while waiting for buffer latch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

A time-out occurred while waiting for buffer latch

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-29 : 11:07:30

I have a SQL Server 2005 Server (32bit) running SQL Server 2005 Standard Edition and SP3.

The Server is a 4 Processor box (Two Dual core CPU's) - each processor at 2.87 GZh

I have 16MB of RAM and three Instances running - one Default and two named.

In my default instance only, I have been seeing this error Intermittently :

A time-out occurred while waiting for buffer latch -- type 4, 
bp 02DA3904, page 1:18248, stat 0x1c0000b, database id: 2, 
allocation unit Id: 141667044818944/423142021529600, task 0x00B851F8 : 
0, waittime 300, flags 0x80039, owning task 0x008D9E28. 
Not continuing to wait.

The error is always associated with Database ID 2. The Database itself appears healthy and I have not had any complaints of permformance issues.

in googling this error, I found things like:
FIX: After you enable the Address Windowing Extensions (AWE) feature in SQL Server 2005, access violations may occur, and SQL Server 2005 may stop responding (Cumulative update package 4 for SQL Server 2005 Service Pack 2 ) where this error is fixed with Cumulative update package 4 for SQL Server 2005 Service Pack 2 (I am already at SP3
-
I also found (in this forum) where there was an incompatiblity issue with a certain version of Diskeeper - we are not running diskkeeper.
--

Description of common causes of SQL Server error message 844 or error message 845 indicates that it could be a setup problem, hardware problem, ... all very generic ...

I'm not sure what to make of this error and how to begin addressing it- Can anyone shed some light on this for me???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-29 : 13:56:00

It's a performance condition. SQL Server was not able to obtain a buffer latch due to the amount of I/O operations. You should consider running PerfMon continuously to capture what your system looks like when the error occurs. I think you'll find spikes of activity around the same time.

Here are some possible solutions:
1. Increase your hardware resources
2. Run only one instance on the server
3. Run 64-bit
4. Enable lock pages in memory
5. Optimize tempdb (dbid=2)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-30 : 08:03:48

Thanks Tara. I also received this this morning:

Along with a bunch more of these errors,
A time-out occurred while waiting for buffer latch -- type 4, bp 03675BE4, page 1:18272, stat 0x3c0400f, database id: 2, allocation unit Id: 141667044818944/423142021529600, task 0x00B853D8 : 0, waittime 300, flags 0x80039, owning task 0x008D9E28. Not continuing to wait.

I also got these errors this morning..

Message
Exception raised in IO Completion Routine. See errorlog for more details

Message
External dump process return code 0x20000001.
External dump process returned no errors.

Message
Stack Signature for the dump is 0x56C2C8E5

It seems like the I\O errors usually happen in the wee hours .. not during business hours when people are pounding away...

I really did think that 16MB would be sufficient for this system with 3 Instances. Each Instance is grabbing it's 2GB and up till now it's been happy..

The server gets rebooted once a month and hasn't been rebooted since mid-feb .. My tempdb for the default instance is set up with default settings.. initial size 8MB data 1 MB log with autogrowth set to 10%. Probably not ideal.. current tempdb size is 500

my plan is to start by changing the intial sizes to 250mb and 10mb (log) and then change the autogrow to 10mb instead of 10%. Then I'll restart the instance. once the instance comes back up.. I will add a second datafile to the tempdb DB and restart the instance again.. Any thoughts on this approach?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-30 : 17:08:13

It's not a memory issue, but rather it looks like an I/O issue. I'm concerned about the "exception raised in IO completion failure" message. Does DBCC CHECKDB come up clean for all databases? Are your disks reporting any errors in the Event Log?

I wouldn't suggest restarting SQL Server twice to make the tempdb changes. If you have 4 CPUs, then you should be adding an additional 3 data files. 4 is what is suggested for your system. Also, I'd suggest a higher increment than 10MB for the tlog to avoid the VLF issue.

I think you need to either contact Microsoft regarding your issues and/or your hardware vendor.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-03-31 : 04:13:07

"I'd suggest a higher increment than 10MB for the tlog to avoid the VLF issue."

Seconded