error: A time-out occurred while waiting for buffer latch 2

[jpotucek]

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:

http://support.microsoft.com/kb/941695/941450

(

http://support.microsoft.com/kb/941450/LN/

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;310834

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

 

[mrdenny]

If you are getting buffer latch timeout error then the hardware is taking to long in allocating that block of memory. This might indicate a bad stick of memory, among other potential problems.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[jpotucek]

Thank you for your reply.. I didn't think about a bad memory stick!!!!

I ruled out a bad drive in the array and most configuration problems except the tempdb.

Since the error always points to Database ID 2 - which is Tempdb - and I am ONLY seeing the error in the default instance.... I was going to schedule a time to reconfigure Tempdb for the default instance.

Since my Tempdb is currently at 350 MB with an 8MB initial size and autogrow set at 10%, My plan is to start by changing the initial size of the Tempdb datafile to 250MB with a 50MB log and setting the autogrow to 50MB - then restart the instance in order for the changes to take effect. I will then add a second Datafile (on a different drive) and restart the instance again.

Any other suggestions??

 

[mrdenny]

You should make the tempdb change no matter what. Having tempdb with the default settings will cause fragmentation on the disk and lead to performance problems.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog