Hi Andy,

We've been encountering the same issue in similar circumstances, albeit we're only using Windows 2003 Standard Server. We're migrating our SQL Server 2000 systems, that have been running 24/7 quite happily since 2001, up to SQL 2008 and finding on one of the servers that memory resource is quite a problem. I don't have an answer yet, but I'll share some of our thoughts.

The first time we saw this was when our web servers started throwing ugly error messages when they called any stored procedures that involved further calls to extended stored procedures. The SQL error logs had corresponding entries with the exact text you describe regarding the internal resource pools.

At this point it's worth mentioning that the Resource Governor is NOT enabled.

Further examination of the SQL logs showed that the memory issue was a clear deterioration over a number of days; prior to the extended sps failing we could see that there were issues with our regular transaction log backup, where the following message is written:

Memory constraints resulted reduced backup/restore buffer sizes. Proceding with 7 buffers of size 64KB.

Going back further in the logs we get to a point where the transaction log backup was working fine but there were issues with Backup Exec which resulted in it dumping the output of dbcc memorystatus to the error log - I'm not familiar with that one, so I need to find out more.

We're running on HP servers that include their Insight Manager software. This creates its own SQL instance to work with, and was never a problem under SQL 2000, but for some reason it now grabs the best part of 500Mb for the database. Together with its resident processes it's using in excess of 750Mb of memory, which for something we don't really need is a bit wasteful. So we've just disabled that and see how it goes over the next few days.

If we do still encounter the problem I might play with some of the other DBCC commands that might help - FREESYSTEMCACHE, FREESESSIONCACHE and FREEPROCCACHE. That might resolve the problem without restarting the database.

Overall, I'm with you Andy; why doesn't SQL start paging when physical RAM gets short? OK, performance will go downhill, but at least it keeps running.

Any further hints will be much appreciated.

Jon