"Not enough storage is available to complete this operation" When Inserting Into SQL BLOB

Yesterday, I had an issue with one of our programs when it was trying to insert a file into the Image field of our SQL Server 2000 database.  The file itself was just under 400MB and the error returned by my program was "Not enough storage is available to complete this operation."  The hard drives and database/logs both had plenty of free space to accommodate this file.  I know that the maximum size of an Image field is 2G, but does that equate to me actually being able to store up to a 2G file?  If not, what is the size of the largest file that will fit into this field?

Thank you!
rtedi222Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
I have seen this before, but it's been specific to the application running -- there was a memory leak.  If your hard drives all have ample storage space, I would check the program for a memory leak.

maybe the connection that is inserting this file isn't closing properly
hence, the memory leak?

this is a big maybe, but i have seen that as the cause of this error before.  
0
dbaSQLCommented:
Oh, also, you didn't specify exactly how you were inserting the file.  
If it's DTS or pre-sp3, it may be this:    http://support.microsoft.com/?kbid=889170
0
rtedi222Author Commented:
We are running SP4 and the INSERT is through a query in my VB application.
0
dbaSQLCommented:
still, this could be the memory leak.   is this something you could use?
http://msdn2.microsoft.com/en-us/library/aa293901(VS.60).aspx

i suppose if it is ram deficiency, it could be specific to sql, too.  first, check tempdb --- if the program is caching heavy in the tempdb and it's not able to grow enough, we could have problems here.
also, how much ram does sql have, and is it allowed to allocate its own memory dynamically?

other than that, i am not too sure.  this is not xml, is it?   per BOL:

Note  A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.