How to increase procedure cache?

[Vadim Nirenberg]

The problem:
"There is not enough procedure cache to run this procedure, trigger, or SQL
batch. Retry later, or ask your SA to reconfigure SQL Server with more
procedure cache.
(return status = -7) "

I am using ASE 11.9.2.

How to increase cache size?

[Sanjay_G]

sp_configure "procedure cache percent", value

then restart ur server

Sanjay

[Justin Cole]

<Sanjay_G> wrote in message
news:AFDB86B00FA69448006E8D7185256B09.006BD91285256B09@webforums...

> sp_configure "procedure cache percent", value
>
> then restart ur server

You may wish to review the total amount of memory allocated too! Increasing
the procedure cache will decrease the data cache size! Chances are that you
are running the default memory allocation and by increasing this the
procedure cache will naturally increase too! (It's a percentage, default
20%)

Look at configuring the total memory:

sp_configure "total memory",x

Or just to check the current setting:

sp_configure "total memory"

Regards,

Justin.

[kohinoor]

At times, this problem appears for servers that have not be bounced for
long. If this is the case, just bounce the server and things should be OK.
However if this message is being seen for a new stored procedure then the
proc. cache can be increased as described previously.

--kohinoor

>The problem:
>"There is not enough procedure cache to run this procedure, >trigger, or

SQL batch. Retry later, or ask your SA to >eeconfigure SQL Server with

[Sanjay_G]

But since Proc cache works on LRU - MRU strategy like data cache
this msg should never be seen
Then what cacuses this, is it a bug????????

[Jim Egan]

Sanjay_G wrote...

> But since Proc cache works on LRU - MRU strategy like data cache
> this msg should never be seen
> Then what cacuses this, is it a bug????????
>

I don't know that I would say "never". If you have a lot of active processes that open
stored procedures it may be possible that there are procedures in memory that are in use
and cannot be removed. Increasing total memory and/or procedure cache percent (or
absolution value in 12.5) often resolves the situation.

It may also be a problem on the client application side where stored procedures are opened
and never closed. (This is a problem I've seen with the ASA product. I don't know if it
can be reproduced on ASE or not.)
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services