PRB: LRU List Empty Errors Can Make the Server Stop Responding

ID: Q177545

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

"Pinning" tables or high configuration values for "RA worker threads" can starve SQL Server of free buffers. This can lead to the following error in the errorlog file and eventually cause SQL Server to stop responding:

spid2 Lazywriter: WARNING, LRU list is empty (1 free bufs, 144 total bufs)

If you encounter this error, you will have to restart SQL Server to continue operation. It is very possible that if this error occurs, it will be repeated in the errorlog every few minutes, and eventually cause the following error:

spid2 Lazywriter: WARNING, No free buffers found within the last 5 minutes.

WORKAROUND

To work around this problem, avoid using high configuration values for "RA worker threads" and use extreme caution and testing when "pinning" tables. If you need to use these features but encounter the error, you may be able to increase the amount of memory reserved for SQL Server.

If using these options causes continual server problems, you should consider changing these options to their default values.

For the "RA worker threads" configuration value, the default is set to 3 upon installation. Use sp_tableoption to "unpin" any tables that are currently marked as "pinned." Note that when a table is marked as "unpinned," the pages associated with the table are not immediately flushed from the cache. They are simply flushed to disk and aged out of the cache according to normal buffer manager algorithms.

MORE INFORMATION

"Pinning" tables can be achieved by using the DBCC PINTABLE statement or the "pintable" option of sp_tableoption. As documented, this provides a method to force pages for a table to remain in cache. However, this also prevents SQL Server from using the buffers associated with those pages. Note that the following warning is displayed after using DBCC PINTABLE or sp_tableoption with "pintable":


   WARNING: Pinning tables should be carefully considered. If a pinned
   table is larger or grows larger than the available data cache, the
   server may need to be restarted and the table unpinned.

The Read Ahead Manager in SQL Server grabs 16 free buffers for each Read- Ahead worker thread as configured by the "RA worker threads configuration option. If the server has a low amount of memory configured, this could lead to starvation for free buffers, and the errors and conditions listed in the SYMPTOMS section of this article. For most systems, the default setting for "RA Worker Threads" is adequate.

If you cannot start SQL Server because of this problem, start the server with the -f command line option and reduce the "RA worker threads" configuration value.

Additional query words: hang hung freeze frozen lock locked up

Keywords : SSrvGen
Version : WINNT:6.0 6.5
Platform : WINDOWS
Issue type : kbprb