June 7, 2006 at 6:17 am
SQL 2000 Data Warehouse - incremental updates
I'm getting this error message, Msg 802, Sev 17: No more buffers can be stolen. [SQLSTATE 42000], when trying to build a table (table does not build).
This table has been build nightly for a little over a year.
I've researched but cannot find any reference to why this error occurring.
Any help would be greatly appreciated.
June 8, 2006 at 7:56 am
From http://www.chriskempster.com/tipsandhints.html
>>
| SQL Server]No more buffers can be stolen | |
| This is a very strange error. From what I can gather its a buffer cache issue related to the server not having enough memory to complete a transaction. The DBA can force this by reducing the memory SQL Server instance uses and altering a large table (say, adding a new column to it via EM). As all IO must go through the buffer cache a lack of free slots will enhaust memory, resulting in the error. If this happens to you whilst saving a script generated via EM (and you are still in the GUI), then attempt the save again and hope for the best, if the error persists, flush the buffer cache in another session and try again. If you dont the uncompleted script can leave your database stucture in an unknown state. Like all things, backup before applying database changes of any type, no matter the simplicity of it. Consider these counters when checking buffer cache issues: SQLServer:Buffer Manager (Free Buffers, Stolen Page Count) SQLServer:Memory Manager (Memory Grants Pending) SQLServer:SQL: Statistics (SQL Compilations/sec) |
<<
June 8, 2006 at 8:27 am
How do I flush buffer cache?
What does GUI stand for?
How do I review/see these counters?
Consider these counters when checking buffer cache issues:
SQLServer:Buffer Manager (Free Buffers, Stolen Page Count)
SQLServer:Memory Manager (Memory Grants Pending)
SQLServer:SQL: Statistics (SQL Compilations/sec)
Thanks for your time!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply