"Not enough storage is available to process this c

Question

Post reply

"Not enough storage is available to process this c

rflewitt

Right there with Babe

Points: 718
More actions
July 17, 2003 at 8:07 pm

#357942

I have developed a VB6 program which runs a stored procedure. Depending on its input parameters this stored procedure runs one of three DTS packages using the sp_OA system stored procs. Several users are using the program. Everything works fine for several hours until at some point in time the sp_OAMethod in the stored proc starts to fail and comes up with an error "Not enough storage is available to process this command". I've found no other way to get over this problem except stopping and restarting SQL Server. There's plenty of memory and disk space etc available at the time when the problem occurs. Here are short descriptions what the DTS packages do:
Package one queries some cube metadata using DSO. Server, catalog and cube name are passed to the package by setting global variables using sp_OASetProperty.
Package two reprocesses an OLAP cube. The TreeKey defining the cube to process is passed from the stored proc to the package by setting a global variable using sp_OASetProperty system stored proc.
Package three runs a SQL command which is passed to the DTS Package by setting a global variable using sp_OASetProperty.
Someone might wonder why I'm doing these things via DTS packages. The reason is that I had to minimize the access rights that the endusers need, especially to avoid making all users OLAP Administrators (which is basically needed to reprocess a cube or use DSO).
We are using SQL Server 2000 SP 3 and Analysis Services 2000 SP 3 on W2K server.

Viewing 7 posts - 1 through 6 (of 6 total)

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

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

Typically I've seen this error when one of the memory pools (but not total server memory) runs out. For instance:

http://support.microsoft.com/default.aspx?scid=kb;en-us;172281

Are there any error messages in the Event Log?

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Maybe completely irrelevant, but do you release all your object explicitly

rflewitt Right there with Babe Points: 718 More actions · Answer 3

We use sp_OADestroy to release to objects.

We alos have not noticed anything interetsing in the Event Log - but will look again. The error Brian pointed me to was an Arceserve problem and the error message was not quite the same as we have been experiencing.

Thanks for your very prompt help so far.

Cheers, Richard

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 4

The ArcServe KB is one example. I've also seen it, for instance, with Lawson Insight, an ERP application. In fact, with Lawson you have to change the memory sizes in the registry because the defaults tend not to be enough on any-sized installation.

You might want to do a KB search on PoolMon which will let you monitor memory usage. The Microsoft Support site has several articles. Unfortunately, you're going to have to watch it real-time and take snapshots. One of the articles details how to do that. I believe Product Support Services has a program that will output to a log file but I don't believe it's readily available for download.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 5

Have a look at

FIX: Invoking SQL-DMO Methods By Using "By Reference" Parameters May Cause a Memory Leak

at

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q293636

rflewitt Right there with Babe Points: 718 More actions · Answer 6

Thanks 5'

We'll take a look at that url. Currently we are sending Microsoft a collection of different profile logs, event logs and anything else that looks like it may help get to the bottom of the problem.

Cheers, Richard