FIX: Buffer Latch Timeouts or Server Sluggishness Occurs When You Remove Procedures From Cache

View products that this article applies to.

This article was previously published under Q309093

BUG #: 355633 (SHILOH_BUGS)

SYMPTOMS

On systems with more than 3 GB of memory that are running with the awe enabled sp_configure option, SQL Server may report latch timeout warnings in the error log as a result of the removal of old plans from the cache. There are other reasons that these errors may be seen, this article is only addressing one potential cause. Here are some of the forms of the error message you may receive:

2001-06-04 17:10:35.55 spid106 WARNING: EC 59218090, 17 waited 7500 sec. on latch 44898144. Not a BUF latch.

2001-08-22 15:35:40.03 spid66 Time out occurred while waiting for buffer latch type 2, bp 0x18ef2400, page 1:79), stat 0xb, object ID 2:3:0, EC 0x43A17530 : 0, waittime 300. Continuing to wait.

2001-06-04 15:13:26.97 spid6 Time out occurred while waiting for buffer latch type 4, bp 0x197c83c0, page 1:7450945), stat 0xb, object ID 13:1495676376:8, EC 0x42F703A0 : 0, waittime 300. Not continuing to wait.

2001-06-04 15:13:26.97 spid6 Error: 845, Severity: 17, State: 1 2001-06-04 15:13:26.97 spid6 Time-out occurred while waiting for buffer latch type 4 for page (1:7450945), database ID 13.

2001-06-04 15:13:26.97 spid6 Error: 845, Severity: 17, State: 1 2001-06-04 15:13:26.97 spid6 Time-out occurred while waiting for buffer latch type 4 for page (1:7450945), database ID 13.

Back to the top

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 (http://support.microsoft.com/kb/290211/EN-US/ ) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Back to the top

Hotfix

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:

   Version      File name
   --------------------------
   8.00.0452    Sqlservr.exe

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

Back to the top

MORE INFORMATION

When you run SQL Server with the awe enabled option, SQL Server may hold a latch while attempting to remove an object from the cache. If that cached object contains information that is stored in memory from a shared memory allocation block, the cache removal may block or deadlock with another system server process ID (SPID) that is accessing the same memory block. As a result, the latch is held longer than anticipated, which may lead to latch warnings and timeouts for other users.

Back to the top