Error 701, not enough procedure cache (*Urgent*)

[Former Member]

Hi All,

Is there a SQL that I can use to determine the SPID that is the root cause of a 701 (not enough procedure cache)?

Additional information:

This is happening in 2 servers; 15G memory with 2G procedure cache and 10G memory with 1.25G procedure cache.

Environment: Sun Solaris 10 running ASE 15.0.3 ESD#4

Monitoring has been enabled.

Sybase recommends either increasing the procedure cache or running dbcc proc_cache(free_unused) periodically.

I would rather identify the process causing it or the configuration setting in these 2 environment that is causing this to happen.

Thanks in advance.

Anil

[Former Member]

Mark,

I am looking for the command that generated the 701.

The 701 errors show up in the ASE log.

The error could show up in multiple application logs as all of them could be the victims of it rather than the originators of the problem.

Wouldn't looking at monSysStatement have the same problem as above? Say SPID 100 was originator of this problem but SPIDs 101,102,103 were running and using the procedure cache and encountered the 701 error, wouldn't the ErrorStatus on all the these SPIDs show 701?

Anil

[Former Member]

Hi Anil,

I think we have find an solution to this it was an error in the application that create bad sql.

Thanks,

Niclas

[Former Member]

Hi,

Can anybody provide me the steps to increase the size of Procedure size

Thaks

Sunil.

[c_baker]

sp_configure "procedure cache size", <number of 2K pages>

go

Chris

[Former Member]

Hi,

Not able to take backup through dbacockpit.

DBSID log file showing below mentioned error messages...please suggest the solution

00:0002:00000:00017:2015/04/09 18:04:09.62 kernel  Job Scheduler Task connected with Agent on port 4903

00:0002:00000:00017:2015/04/09 18:04:10.62 kernel  Job Scheduler Task got spurious job states from its Agent

[former_member3043]

Hello Sunil,

I would suggest that you post this in a new thread, to many questions related to different topics in one single thread are confusing especially when this thread is from August last year.

For the JS problem, take a look to SAP Note 1835880, this should help to get more details what is going wrong. Also you should check the <SID>_JSAGENT.log if this will help to get closer details to the problem.

Regards

Stefan

[former_member187136]

Sunil,

To increase Proc cache:

Max Memory Allocated: <Value_Configured> X 2K = Primary Memory Allocated

Proc Cache = <Value_Configure> X 2K = Proc Cache

If you cannot able to increase because of main memory scarcity, first increase the main memory and try increasing the proc cache.

To increase Proc Cache:

1. Estimate the Proc cache:

1> sp_helpconfig "procedure cache size","<Required_Memory>"

        2> go

2. If the first step is ok, then increase it by running the below command

      1> sp_configure "procedure cache size", <Required_Memory>

      2> go

PS: 2K - Page Size which you can get from the below outputs

1> sp_monitorconfig “max memory”

2> go

1> sp_configure “max memory”

2> go

1> sp_monitorconfig “procedure cache size”

2> go

1> sp_configure “procedure cache”

2> go

Regards

Kiran K Adharapuram

[former_member187136]

Sunil,

For the JS_Agent Issue:00:0002:00000:00017:2015/04/09 18:04:10.62 kernel  Job Scheduler Task got spurious job states from its Agent

Try restarting the JS_Agent and if possible drop and create the jobs again.

Steps to follow for agent restart through isql:

use sybmgmtdb
go
exec sp_sjobcontrol @name=NULL,@option="stop_js"
go
dbcc traceoff(3605,3641)
go
exec sp_sjobcontrol @name=NULL,@option="start_js"
go

More Details is under SAP Note: 1835880 - SYB: How to diagnose issues with Sybase ASE Job Scheduler, ATM, or Scheduled Jobs

Regards

Kiran K Adharapuram

[Former Member]

Dear all,

Issue has been resolved... Thanks all for your support

[former_member187136]

Sunil,

Nice to hear.

Lets close this thread so that our fellow DBA's will use this for 701 errors

Regards

Kiran K Adharapuram

[former_member187136]

Hi All,

I created a SAP Note for this:

2155469 - [ASE Error SQL701] THERE IS NOT ENOUGH PROCEDURE CACHE

Check, If I can add some more points in this SAP note , I will modify and add the same if the note is incomplete.

Regards

Kiran K Adharapuram

[former_member188958]

Another thing you might try is the dbcc memusage command.  It will display the 20 biggest things in procedure cache, so if a few extraordinarily large cached procedures are the cause of the 701s, this may identify what the objects are.  It won't identify who first ran it, though, or even show if any spid is currently executing any of those 20 items.

Example:

1> set switch on 3604
2> go
Switch 3604 ('print_output_to_client') is turned on.
All supplied switches are successfully turned on.
1> dbcc memusage
2> go
Memory Usage:

                                   Meg.           2K Blks                Bytes

      Configured Memory:       156.2500             80000            163840000
Non Dynamic Structures:         5.3751              2753              5636252
     Dynamic Structures:       123.1582             63057            129140736
           Cache Memory:         9.0098              4613              9447424
      Proc Cache Memory:        17.5703              8996             18423808
          Unused Memory:         1.1191               573              1173504

Buffer Cache Memory, Top 8:

Cache           Buf Pool        DB Id   Partition Id    Index Id         Meg.

default data c                31515         8             0            0.0117
                      2K      31515         8             0            0.0117
default data c                    1         8             0            0.0039
                      2K          1         8             0            0.0039
default data c                    4         8             0            0.0039
                      2K          4         8             0            0.0039
default data c                    2         8             0            0.0020
                      2K          2         8             0            0.0020
default data c                    3         8             0            0.0020
                      2K          3         8             0            0.0020
default data c                    5         8             0            0.0020
                      2K          5         8             0            0.0020
default data c                   14         8             0            0.0020
                      2K         14         8             0            0.0020
default data c                31514         8             0            0.0020
                      2K      31514         8             0            0.0020

Procedure Cache, Top 20:

Database Id: 31514
Object Id: 121048436
Object Name: sp_downgrade_esd
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Bytes lost for alignment 0 (Percentage of total: 0.000000)
Number of plans: 1
Size of plans: 1.217173 Mb, 1276298.000000 bytes, 628 pages
Bytes lost for alignment 6488 (Percentage of total: 0.508345)

----
Database Id: 31514
Object Id: 905051229
Object Name: sp_do_poolconfig
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Bytes lost for alignment 0 (Percentage of total: 0.000000)
Number of plans: 1
Size of plans: 1.080750 Mb, 1133248.000000 bytes, 582 pages
Bytes lost for alignment 3674 (Percentage of total: 0.324201)

----
Database Id: 31515
Object Id: 1344004788
Object Name: sp_dbcc_run_faultreport
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 1
Size of trees: 0.972553 Mb, 1019796.000000 bytes, 501 pages
Bytes lost for alignment 4609 (Percentage of total: 0.451953)
Number of plans: 0
Size of plans: 0.000000 Mb, 0.000000 bytes, 0 pages
Bytes lost for alignment 0 (Percentage of total: 0.000000)

[...]

[jayrijnbergen]

regarding dbcc memusage

- does it still freeze the ASE server to take the snapshot?

- I remember there was some issue with this command when using multiple engines, is that still the case?

[former_member3043]

Hello,

hard to find the root cause for this. One idea is to use your monitoring and capture the data frequently to a repository, if you get an 701 error reported in the errorlog you can use the spid to find any details that might was captured in the MDA tables, to identify what what this process tried to do.

The error should be reported in the log and the first columns are <engine>:<familyid>:<spid>:<timestamp> server Error: 701 ....

The montables that could be interesting are in the monProcess (monProcess, monProcessObjects and so on) group. Give it a try.

Hope this helps.

With kind regards

Stefan

[jgleichmann]

Please provide the output of the following command:

sp_monitorconfig "procedure cache"

go

[Former Member]

Jens,

For the first server:

1> sp_monitorconfig "procedure cache"

2> go

Usage information at date and time: Aug  1 2014  1:46PM.

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt 

------------------------- ----------- ----------- ------- ----------- -----------

procedure cache size           609397       30603   4.78       639522       14797

(1 row affected, return status = 0)

For the second server:

1> sp_monitorconfig "procedure cache"

2> go

Usage information at date and time: Aug  1 2014  1:48PM.

Name                      Num_free    Num_active  Pct_act Max_Used    Reuse_cnt 

------------------------- ----------- ----------- ------- ----------- -----------

procedure cache size           962501       61499   6.01      1024000      167236

(1 row affected, return status = 0)

Anil

[jgleichmann]

The output show us that the procedure cache is not sized properly for your system, because it was used to 100%. Please increase the size of it.

Which statement caused it is hard to find out. You can try Bret's variant or if you have installed SCC (Sybase Control Center) you can see it in the procedure cache tab.

But it is the sum of the statements which fill up the cache - not only one statement.