Memory Error Entries in SQL Server 2000

By: Edgewood Solutions | Read Comments | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More

Problem
You're reviewing your SQL Server error logs and a series of words catch your eye...STOLEN, DIRTY, KEPT, WAITING. And those words are surrounded by a bunch of numbers. The sight is more than a little ominous, particularly if you've never seen it before. So what is it? Quite simply, it is the status of memory allocation after a memory exception. But finding the reason why your server decided to throw all of this information in the log can be a little tricky. Here are some ideas on why it happened.

Solution
You'll see these SQL error log entries on servers that have Address Windowing Extensions (AWE) enabled. Although there are a number of reasons for the appearance, a good number of them are because of logic issues inside SQL Server itself. Times when you could potentially see these output messages include when:

• sqlmaint.exe is used with a Maintenance Plan (specifically when rebuilding indexes using the -RebldIdx switch)
• Excessive outer joins are used in a query
• Extreme number of tables, confusing the lazywriter process

Here is an example of these listings from a SQL Server 2000 error log.

When you see these entries in the error log, the first thing to do is to note the time and frequency. Next, check the scheduled jobs on the server to see if there is a correlation between when a job runs and when the entries appear. Finally, run a Profiler trace at the time you expect the error to occur (based on frequency in the error logs) to see if memory exceptions or hash warnings appear. The good news is that Microsoft is aware of these situations and have dealt with them through service packs and hotfixes.

Next Steps

• Read some of the Knowledge Base articles related to these memory entries:
  • Building/re-building idexes on an AWE-enabled server may cause BPool::map warnings
  • Sorting/hashing issues that can cause these entries
  • Extremely large number of tables on an AWE-enabled server may cause BPool::Map errors
  • Queries with multiple outer joins may cause excessive optimizer memory consumption
  • Cursors That Have a Long Lifetime May Cause Memory Fragmentation
• If you encounter these entries in your error logs, determine which service pack has been applied to the server
• Review information on Address Windowing Extensions (AWE) on MSDN

Related Tips: More | Become a paid author

Last Update: 1/15/2007

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST