Database connections exceeded or unable to connect

[DougRulison]

We've run into this problem a couple times now during testing.

After several users have launched the web application and we go back to doing desktop work we run into a couple errors (see screen shot) and  the other error message is "have exceeded the maximum number of connections".  If someone was alreay using a database connection they could still work but no additional data connections coule be made.  Also, we could still use the web app and all data displays without problems.

I've checked with our dba and he says we are not eve nclose to exceeding our db connection limit.  So what gives?  Is there a setting in ArcServer that will reduce or eliminate the number of database connections?  Is this an SDE issue or an ArcServer issue?

Doug

[LuciHawkins]

Hey Doug,

Not sure if you are having the same problem that we were experiencing.  By default when you create a new geodatabase the maximum number of connections to the database is 48.  That was ample with 10.0 but with 10.1 we were having problems.  We first noticed that we were running out of connections in the 10.1 Arcgis Server log.  It seems as though every one of the 70 services in Arcgis Server was making a connection to the database so we were already over 20 connections short.  I called tech support and they showed me how to use the SDE command line interface to change the maximum number of connections to the database.  I changed it to 200 and we have not had a problem since.  The table that we changed was sde.SDE_server_config and the field was CONNECTIONS. 

Hope this helps,

Luci

[DerekLaw]

Hi Doug,

Just to add onto what Luci posted, some help resources:


The SERVER_CONFIG system table

Altering SERVER_CONFIG parameters



Hope this helps,

[SebastianRoberts]

I ran into the same issue today on our publication database which is only used for map services.  I'm running ArcGIS Server 10.1 with MSSQL server.  I have about 70 services, but ArcCatalog shows only 4 instances currently running on one of those services, yet SQL server shows 252 active connections.  Any idea how to further research why I have all these open connections? 

Second, the CONNECTIONS field in the SDE_server_config table has a value of 64.  So how can the "gisweb" SQL user (used for mapservices) have 252 connections active?

Thanks for your help,
Sebastian

[NidhinKarthikeyan]

This behavior is a result of orphaned connection. You can set TCPKEEPALIVE = TRUE to remove orphaned connections. As Law suggested, you can 'Alter SERVER_CONFIG parameter'. However it should be limited to the memory of the machine.

[NidhinKarthikeyan]

A script can be used in Oracle to clean 'Process_Information Table':

set SERVEROUTPUT ON 
exec dbms_output.enable(100000); 

DECLARE 

CURSOR process_list IS 
SELECT sde_id, owner, nodename FROM sde.process_information; 

lock_name VARCHAR2(30); 
lock_handle VARCHAR2(128); 
lock_status INTEGER; 
cnt INTEGER DEFAULT 0; 

BEGIN 

FOR check_locks IN process_list LOOP 

lock_name := 'SDE_Connection_ID#' || TO_CHAR (check_locks.sde_id); 
DBMS_LOCK.ALLOCATE_UNIQUE (lock_name,lock_handle); 
lock_status := DBMS_LOCK.REQUEST (lock_handle,DBMS_LOCK.X_MODE,0,TRUE); 

IF lock_status = 0 THEN 
DELETE FROM sde.process_information WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.state_locks WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.table_locks WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.object_locks WHERE sde_id = check_locks.sde_id; 
DELETE FROM sde.layer_locks WHERE sde_id = check_locks.sde_id; 
cnt := cnt + 1; 
dbms_output.put_line('Removed entry ('||check_locks.sde_id||'): '||check_locks.owner||'/'||check_locks.nodename||''); 
END IF; 

END LOOP; 

/* Remove any orphaned lock entries... */ 

DELETE FROM sde.state_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
DELETE FROM sde.table_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
DELETE FROM sde.object_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
DELETE FROM sde.layer_locks WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 

COMMIT; 

dbms_output.put_line('Removed '||cnt||' entries.'); 

END; 
/ 

It remove any orphaned entries in the table which can prevent new connections (because of maximum connection).

[SebastianRoberts]

Nidhin,
  Thanks for your reply.  I will bump up the CONNECTIONS parameter.  I'm not sure what you mean by "it should be limited to the memory of the machine".  Our database server has 8 GB of RAM, but we also have about 20 ArcMap users making connections to our production database on the same server.  Do you know where I can get more direction on what is a reasonable number of connections to allow.
  Also, I will try setting the TCPKEEPALIVE parameter to true.  I do have all map services set to "periodically check and repair data connections for idle instances" and to recycle every 24 hours at midnight.  However, even though we have very little web traffic over night, the number of open database connections didn't decrease at all over night, and I had to restart our application server (running ArcGIS server) this morning in order to release the idle/broken connections on our Database server.  Does the TCPKEEPALIVE parameter work differently than the two settings that I mentioned above?
Thanks again for your help,
Sebastian Roberts

[NidhinKarthikeyan]

The maximum number of connection is database specific(limit).

It should be limited to the memory of the machine

Its about Windows host. Windows will exhaust its 'non-interactive desktop heap'. I would suggest you to take a look at this technical article.

Setting the parameter, TCPKEEPALIVE = TRUE for ArcSDE removes any orphaned gsrvr processes. However TCPKEEPALIVE does not clean up entries in the SDE.PROCESS_INFORMATION table.

You can create a batch file to restart your service and then create a scheduled task on SDE server that runs this batch file 'at system startup'. It will remove orphaned connections.

[RandyKreuziger]

The technical article link is broken for me but I know what you're referring to.  However, does the memory issue only apply to 3 tier connections only or does it include direct connects?