Insufficient memory error

Question

Insufficient memory error

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 25, 2007 at 6:26 am

#69037

Hi
We are getting the following error on one of our servers:
'Error 22022: Unable to post notification to SQL
ServerAgent (reason: MapViewOfFile() returned error
8, 'Not enough storage is available to process this
command')
This causes that none of the jobs on the database to run. A restart of the SQL Service solves the error for the time being.
I read a thread and someone said that the SQL Server Agent Job log was full. Does anyone know where I can find this and can the job logs be deleted?
Regards
IC

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

In SQL Server 2000, the SQL Server Error Log can be found in Enterprise Manager at >Management>SQL Server Logs. Running the command sp_cycle_errorlog in Query Analyzer cycles the log. Reading the log, at the beginning you will see an entry that gives the path to where you can find the log file itself and open it in notepad (this is helpful as the log can be too large to open in Enterprise Manager).

In SQL Server 2005, there are two logs. The Error Log can be found at Management>SQL Server Logs and the Agent log can be found at SQL Server Agent>Error Logs. Use the same command as in 2000 for cycling the Error Log. For the Agent log use sp_cycle_agent_errorlog.

-SQLBill

Jeff Gray SSChampion Points: 10667 More actions · Answer 2

Hi Imke,

What is the response to

Select @@version

On your server?

Minaz Amin SSChampion Points: 11052 More actions · Answer 3

What is memory configuration of the SQL Server. ?

1. Check if there is fragmentaion of the virtual memory then defrag it.

2. Check the output of the DBCC MEMORYSTATUS shown where the memory have been used.

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 4

Jeff - Response from select @@version:

Minaz -

the virtual memory has already been defragged.

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

As SQLBill, try to recycle the log.

What is the total server memory ? What is the max / min SQL server memory?

is there /3GB is enabled in the Boot.ini ?

"More Green More Oxygen !! Plant a tree today"

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

Do you have any messages in the SQL server error log similar to this:

WARNING: Failed to reserve contiguous memory of Size= 131072.

Also, what is the function MapViewOfFile() doing?

Does it contain any XML manipulation or OA_Create code?

The reason I ask is that this cold be caused by having insufficient 'MemToLeave' memory. The storage referred to by the error message is (I'm pretty sure) memory, not disk. This memory is used by SQL server to deal with COM objects, and these are typically created by the OA_Create and sp_xml_preparedocument procedures. Also, third-party backup products will create com objects to make use of VDI.

In one of the systems that I support, we use XML to pass a large number of keys into a sproc. Sometimes this XML document is very large, and I've had to increase the MemToLeave area from the default (256) to 384 or the XML prepare step will fail.

So, I don't know if you are having the same problem or same kind of problem, but if this failing procedure call is a SQL procedure that is creating COM objects then I suspect that the problem may be the MemToLeave area.

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 7

Jeff,

Yes, the error does display like this in the error log:

WARNING: Failed to reserve contiguous memory of Size= 131072.

Can u perhaps explain to me what MemToLeave is and how can I increase this?

Thanks

IC

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 8

Jeff,

Yes, the error does display like this in the error log:

WARNING: Failed to reserve contiguous memory of Size= 131072.

Can u perhaps explain to me what MemToLeave is and how can I increase this?

Thanks

IC

Jeff Gray SSChampion Points: 10667 More actions · Answer 9

Based on the existence of that additional message, my guess is that you are having the same kind of problems that I was having with the MemToLeave area.

See the "Fourth Section" in this KB document for a semi-description of MemToLeave.

http://support.microsoft.com/kb/271624/en-us

In order to increase the MemToLeave area, you need to add a startup parameter like so:

-g384

This example will increase from the default (256) to 384 MB. The startup parameters can be adjusted in the properties page for the server in Enterprise Manager on the "general" tab. Near the bottom of the page, there is a "startup parameters" button.

From what I've seen, most people have only changed this value on advice from PSS, and I followed no such advice. The good news for me is that it worked.

This problem was a lot worse for me before I discovered that our XML consuming stored procedure didn't always make it to the code that released the prepared XML document. Even after correcting that mistake, I found that I had to increase MemToLeave.