ArcSDE Direct Connect: Maximum number of connections to instance exeeded

[StefanP__Jung]

Hi,

we have a lot of ArcGIS Server projects using direct connect to consume Data from an Oracle Database. Currently we ofter have the problem that the ArcGIS Server runs into problems connecting to the SDE Repository (Oracle DB)

If this happens and we try to connect to the Repository via ArcCatalog we get this message: "Failed to connect to database. Maximum number of connections to instance exeeded". This happens when using direct connect but also when using the ArcSDE itself. In this case all users are blocked, even the SDE user itself. Trying to connect to the oracle database with a SQL Client works fine at the same time.

I saw that there is a max connection parameter for SDE Connections - set to ~ 48. Does this also effects direct connect?

Is there any other limitation that could be the reason for this?

On Oracle side i did not found any limit that got reached. For example the max number of processes are not on the limit etc.

More Information coming.

Would be great if any one already got some hints..

Stefan

[VinceAngelo]

Yes, absolutely, the CONNECTIONS parameter controls the number of total connections
(sum of app server and Direct Connect). You can update the value with the 'sdeconfig'
utility or by correctly updating the row in SDE.SERVER_CONFIG.

Keep in mind that Windows hosts have their own upper limit of application server
processes (the "non-interactive desktop heap" issue), which is independent of both
Oracle and ArcSDE (generating a strange error message somewhere after 66
connections on boxes without the registry hack, and ~110 connections with it).

- V

[StefanP__Jung]

hi,

thanks for the quick response.

So it set the max connections to the whole repository.

Is there also a way to monitor how many connections are used via direct connect? Or can i only monitor this within oracle itself. Would be very good to compare it, maybe we have some dead connections etc.

Some details i forgot:

System: Windows 2003 Server
SDE: ArcSDE 9.3 SP 1
AGS: ArcGIS Server 9.3.1 (no SP)
Oracle: Oracle10g (10.2.0.3)

Stefan.

[VinceAngelo]

The SDE.PROCESS_INFORMATION table tracks connections.  You can use a number of ways
to process this information (including 'sdemon', SQL, and a trigger on the table).

- V

[RandyKreuziger]

Yes, absolutely, the CONNECTIONS parameter controls the number of total connections
(sum of app server and Direct Connect). You can update the value with the 'sdeconfig'
utility or by correctly updating the row in SDE.SERVER_CONFIG.

- V

We have multiply instances of SDE on our database instance.  Does this mean we have to up the total connections in all SDE databases?  I ask because we have had problems connecting to a SDE instance that only 2 other users are connected to but one of the other SDE instances has 40 + connections.  ArcGIS Server seems to leave orphaned connections to our SDE 9.2 databases.

[VinceAngelo]

No, each instance is independent with respect to connection count. However, the Windows
heap issue is cumulative.

- V

BTW: The 'sdeping' utility of se_toolkit can be used to diagnose connection count issues --
It has a "-c" option to make multiple connections and a "+DELAY" option to hold onto
them (if it succeeds):

C:\>set SDEUSER=XXXXX
C:\>set SDEPASSWORD=*****
C:\>set SDEINSTANCE=5153
 
C:\>sdeping -c 80 -v +DELAY 17:10
 
ArcSDE 9.3 Connection Test Utility       Thu Jun 10 17:05:23 2010
------------------------------------------------------------------------
         Server: YYYYY
       Instance: 5153
       Database: eval93
       Username: XXXXX
          RDBMS: PostgreSQL
        Version: 9.3.1
    Description: PostgreSQL Build 2784(PostgreSQL)
     Build Date: Tue Oct 27 10:51:14  2009
        Release: 93005
    Connections: 1   2   3   4   5   6   7   8   9   10  11  12
                 13  14  15  16  17  18  19  20  21  22  23  24
  Connect Delay: 333.33 ms (avg)
  Connect Stats: 312.00 ms (min) / 656.00 ms (max) / 69.32 ms (stddev)
 
     Connection attempt 25 failed --
          Error: Operation Failed (-1)
 
  Disconnecting: 24  23  22  21  20  19  18  17  16  15  14  13
                 12  11  10  9   8   7   6   5   4   3   2   1

[StefanP__Jung]

I've just checked our database to see what configuration we currently using. The max connections value is already set to 60 - so acording to the "windows non-interactive desktop heap" issue you mentioned it is not a good idea to set this to a much higher value.

Do you have some more information about this windows issue?

When using multiply instances of SDE the windows issue would still be the limitation right? So if we install 2 instances - and each has 60 connections we would run into the windows issue in worst case (even with the hack)?

[VinceAngelo]

You should set the CONNECTIONS parameter to the number you need (plus a few more).

The N-I desktop heap issue is documented in the Knowledge Base (found by search on
"arcsde desktop heap"). It only applies to application server connections, so you'd need
110+ 3-tier connects before you'd need to take more exotic action.

- V

[RandyKreuziger]

I've upped the "windows non-interactive desktop heap" on all of our servers in the past so I'll start moving some connections to direct connect.  However, is the non-interactive desktop heap since so limited at Windows server 2008 64-bit?

It can take a nearly a year to upgrade all our ArcGIS Desktop clients which in the past meant upgrading SDE last.  We didn't use direct connect because the Desktop and SDE had to match version and service pack exactly.  That's no longer the case correct?

[VinceAngelo]

I haven't tested Windows 2008, but it was still present at 2003 R2 (64-bit). I convinced my
client to run a Linux ArcSDE server; I ran one of them to 400 connections, just for thrills
(but had to raise the Oracle processes parameter to 420 first).

Direct Connect is more forgiving, to a point, but you still need to have the compatible
DLLs available (which is another install, of sorts).

- V