There is insufficient system memory to run this qu

Every few days we get the error message "There is insufficient system memory to run this query. " When running the transaction log backup. The only way to fix it is to reboot.

But this does not make sense. It has 8 gibs of ram with awe enabled. Win2003 service pack 1 and 4 xeon processors.

Any advice would be appreciated.

thanks

How do you optimize the transaction log backup?

We run the transaction log backups every 15 minutes. Actually u know what, this may have started happening after we changed the transaction log backups from 1 hour to 15 minutes. Could that be causing it, I wonder. You may have hit on something. Should we change it back to one hour and see what happens.

Thanks

I have changed the transaction log backups back to 1 hour and will monitor it the next few days.
I will let you know.
I appreciate your help.
Thanks

What version of SQl Server are you using?
It is indicated that AWE is enable, that reminds that SQl Server does not have that setting. So, it must be 2000.

Changing the transaction log backup from 15 mins to 1 hour might not be the root cause of the problem. Reason when doing it 15 minutes sql server has a lot less work to do than when done in 1 hour.

I just checked today and it is still happening, even though the tran log is backing up every hout instead of 15 mins.


I don't know what else to try. We may have to contact microsoft support on this one.

It is a memory related error

Run select * from sysprocesses when you have the transaction log backups running.

see the most of the waittypes and wait resources and wait times.

If you see some thing like Resource_semaphore look for Memory grants pending and Memory grants outstanding.

If you find these values consistently more then there are some queries that are doing large hash joins.

do a update statistics with full scan and
dbcc dropcleanbuffers and dbcc freeproccache to force all the execution plans to be regenrated.

The following is what microsoft told us.

Apparently processing data using a linked server specifially the fox pro driver causes memory leakage which builds up over time.

when ever you get an insufficient memory message,
its talking about virtual memory, the private address space each application process gets,
not physical memory,
so when you have a memory leak, it eventually runs out of address space, and a call to virtualalloc fails, reported as insufficient memory,
regardless of how much physical memory is available

Then if it is the case did you get any other error meesage like:
Failed to reserve contiguous memory of size in the error log.

If it is related to linked server, starting sql server with -g parameter or bringing the driver for fox pro out of sql server address space should work.

What did the PSS guys do?

In addition to message 'There is insufficient system memory to run this query', another frequent error message is there in sql server error log :
"Downgrading backup buffers from 960K to 64K". We are not using any linked server.