Thursday, March 29, 2012

Failed to reserve contiguous memory

Hello,
(I have to repost it under different header since I didn't get any comment
or answer)
We faced a problem running Perl script which creates big XML file and
inserts it into ntext data type column of the table.
When XML file is relatively small â' the process runs OK, but with big files
we get the following error appearing in the SQL server log:
2006-04-06 16:01:13.97 spid117 WARNING: Failed to reserve contiguous memory
of Size= 19333120.
2006-04-06 16:01:14.05 spid117 Query Memory Manager: Grants=5 Waiting=0
Maximum=152183 Available=151908
2006-04-06 16:01:14.05 spid117 Global Memory Objects: Resource=2921
Locks=29503 ...
2006-04-06 16:01:14.05 spid117 Dynamic Memory Manager: Stolen=34679 OS
Reserved=5272 ...
2006-04-06 16:01:14.05 spid117 Procedure Cache: TotalProcs=763
TotalPages=2142 InUsePages=899
2006-04-06 16:01:14.05 spid117 Buffer Counts: Commited=337792 Target=337792
Hashed=302526...
2006-04-06 16:01:14.05 spid117 Buffer Distribution: Stolen=32594 Free=530
Procedures=2142...
I checked several common things posted by Microsoft such as network packet
size and sql_variant data type â' there is no any connection to our problem.
Surprisingly, but the same insert works OK when performed from query
analyzer, even for a big files.
There quite amount of posts on the issue implying MemToLive
configuration, however this is a production server, I can't 'play' with it.
I would be very grateful for your help.
Thanks,If you can get the same hardware configuration and the same volume of
the same type of queries in a test environment, then you could probably
recreate your scenario there and "play" with fragmentation in
MemToLeave. When we called Microsoft, it came down to playing with the
-g parameter to increase the amount of memory allocated in MemToLeave
at startup. But still, that only works to a point because the
fragmentation isn't predictable - which is why it will work sometimes
(ie when you run it through QA) and fails at other times... We've just
had to push it back to developers to implement retries.
This just reinforces my opinion that all those nice little non-database
add-ons made to attract developers to SQL Server should really just
never be used...
BB|||If the same insert works ok when performed in QA, even for a big file.
That narrows the problem down to the perl script rather. It could be a
time out issue. What library you use to connect to SQL Server in perl?
May be you should post more details in the perl user group and see if
people have similar experiences.
Mel

No comments:

Post a Comment