"Waiting for Buffer Latch" Error

[Bob]

Does anybody know what might cause the following message to show up in
the SQL Server Error Log?:

Time out occurred while waiting for buffer latch type 2, bp
0x12260f80, page (5:77914), stat 0x40d, object ID 7:421576540:0,
waittime 500. Continuing to wait.

I've read several articles about what to do about this situation on
SQL Server 2000, but I'm running SQL Server 7.0. Specifically, I'm
running version 7.00.842. Is there a way to resolve this problem
without upgrading to some flavor of SQL Server 2000?

[Erland Sommarskog]

Bob (bo******@enron.com) writes:

Does anybody know what might cause the following message to show up in
the SQL Server Error Log?:

Time out occurred while waiting for buffer latch type 2, bp
0x12260f80, page (5:77914), stat 0x40d, object ID 7:421576540:0,
waittime 500. Continuing to wait.

I've read several articles about what to do about this situation on
SQL Server 2000, but I'm running SQL Server 7.0. Specifically, I'm
running version 7.00.842. Is there a way to resolve this problem
without upgrading to some flavor of SQL Server 2000?

I have no experience of this message, least of all on SQL7. But from
what I read out on the KB articles with the string "waiting for buffer
latch", my suggestion is that open a case with Microsoft. At least if
these messages comes along with problems on the machine. (If all is
well and fine, and you just happened to find theses message by chance
in the log, it may be benign.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bandettini]

Bob

I had some buffer latch problems on a SQL Server 7 system and found it
very difficult to find any help.

While it might not be the same if I tell you what caused my problem it
may point you in the right direction.

Using the object id as a key, you should be able to see what object is
being affected, then you need to find out what process is causing the
problem.

In my case what was happening was a stored procedure was running that
updated a fairly large table (Around 80,000,000 rows). Due to the data
source there was a small duplication of data. When the stored
procedure completed a second stored procedure kicked off that deleted
the duplicate rows.

What seemed to happen was that although the inserts had completed,
they had not been written to disk. As the deletes started, a
checkpoint kicked in that was trying to write the data to disk. I
can't explain exactly what happened, but some sort of conflict between
the inserts, deletes and checkpoint procedure just caused the sysytem
to hang and buffer latch messages to be produced. The only way to
resolve the problem was to reboot the system.

To confirm this was the problem and provide a quick work around, I
added a third step to the process. After the inserts, I performed a
checkpoint and only ran the delte process after the checkpoint had
completed. This solved the problem. As a longer term solution, I
changed the insert process to not insert duplicate rows.

I hope this might help.

John