Microsoft KB Archive/221978

= BUG: Using PINTABLE on SYSOBJECTS May Cause Memory Starvation =

Article ID: 221978

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q221978



BUG #: 18617 (SQLBUG_65)



SYMPTOMS
When either DBCC NEWALLOC or DBCC CHECKALLOC is executed on a database in which the sysobjects table has been pinned in memory, many pages from other tables may also become pinned, causing the data cache to become stressed. SQL Server may appear to be slow or to stop responding and the following errors may be reported in the error log:

spid21 Lazywriter: WARNING, LRU list is empty (1 free bufs, 3945 total bufs)

bufwait: timeout, BUF_IO, bp 0xf2c220, pg 0x1049, stat 0x801100/0x6, obj 0, bpss 0xfbe468



WORKAROUND
Do not pin the sysobjects table. Use DBCC UNPINTABLE to mark the sysobjects table as no longer being resident in RAM.

For more information on DBCC PINTABLE and DBCC UNPINTABLE, see the SQL Server Books Online.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.



MORE INFORMATION
The documentation on the use of DBCC PINTABLE states that if the table exceeds the data cache size it can lead to data cache depletion. The symptoms that occur with this bug indicate that even though the sysobjects table is clearly smaller than the data cache, other pages are pinned in memory, which may then consume a large part of the data cache.

You can use DBCC SQLPERF(LRUSTATS), as well as DBCC SQLPERF(LRUSTATS2) introduced in SQL Server 6.5 Service Pack 2, to monitor the cache usage; for more information, see the SQL Server Books Online (and Service Pack Readme.txt).

You can use the statement below to identify whether a system table has been pinned in the current database.

NOTE: The schema and meaning of columns in system tables may change between product versions.

select name, id from sysobjects where type = 'S' and sysstat & 0x200 = 0x200 go

The result set will list the names of any system tables that have been pinned and the object ID that should be used in the DBCC UNPINTABLE statement.

Additional query words: errorlog resultset hang hangs hung

Keywords: kbbug kbpending KB221978

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.