You are here: > ESRI Forums > arcsde (9.1 and older) users online discussion forum > Thread Replies

ArcSDE (9.1 and older) Users Online Discussion Forum

ArcSDE: General forum

ArcSDE 9.1 max number connections   Katie Lysons Jan 16, 2006
Re: ArcSDE 9.1 max number connections   z ma Jan 16, 2006
Re: ArcSDE 9.1 max number connections   Katie Lysons Jan 17, 2006
Re: ArcSDE 9.1 max number connections   Leo Foretich Jr Jan 19, 2006
Re: ArcSDE 9.1 max number connections   gulam irfani Jan 24, 2006
Re: ArcSDE 9.1 max number connections   Steve Bennett Jan 20, 2006
Re: ArcSDE 9.1 max number connections   z ma Jan 25, 2006
Re: ArcSDE 9.1 max number connections   Steve Bennett Jan 27, 2006
Re: ArcSDE 9.1 max number connections   Thomas Brown Jan 27, 2006
Re: ArcSDE 9.1 max number connections   Keith Shaw Jan 30, 2006
Re: ArcSDE 9.1 max number connections   Thomas Brown Jan 30, 2006
Re: ArcSDE 9.1 max number connections   tony david Jun 25, 2006
Re: ArcSDE 9.1 max number connections   jerry cox Jul 25, 2006
Re: ArcSDE 9.1 max number connections   Katie Lysons Jul 31, 2006
Re: ArcSDE 9.1 max number connections   xiannian chen Aug 01, 2006
Re: ArcSDE 9.1 max number connections   xiannian chen Aug 01, 2006
Re: ArcSDE 9.1 max number connections   Steve Tomlins Aug 02, 2006
Re: ArcSDE 9.1 max number connections   tony david Sep 25, 2006
Re: ArcSDE 9.1 max number connections   Katie Lysons Sep 26, 2006
Re: ArcSDE 9.1/9.2 max number connections   Richard Daniels Mar 30, 2007
Re: ArcSDE 9.1/9.2 max number connections   A.J. Romanelli May 31, 2007
Re: ArcSDE 9.1/9.2 max number connections   Bill Rose Jan 11, 2008
Re: ArcSDE 9.1/9.2 max number connections   Bill Rose Jan 11, 2008
Re: ArcSDE 9.1/9.2 max number connections   Bill Rose Jan 14, 2008
Re: ArcSDE 9.1/9.2 max number connections   Aaron Andrus Jun 10, 2008
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject ArcSDE 9.1 max number connections 
Author Katie Lysons 
Date Jan 16, 2006 
Message We're having problems in that we're consistently exceeding the maximum number of connections to ArcSDE. We have a number of different clients connecting to ArcSDE, ArcIMS, web services and ArcGIS desktop users. We've increased the number of connections (by amending the CONNECTIONS value in the server_config table) to 250 but for some reason the number of connections reaches 201 and then does not allow any more.

We're using ArcGIS 9.1 with Oracle 9i.

Is there some inherent max connections allowed by SDE that we can't exceed? As far as I'm aware this isn't being caused by Oracle limits or server swap space.

Thanks in advance,
Katie 
  Katie Lysons

~~~~~~~~~~~~~~~~~~~~~~~~~
Geospatial Information Analyst
Network Rail, UK
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author z ma 
Date Jan 16, 2006 
Message hi katie,

we are having similar problem in our SDE setup. the new Service Pack 1 may help to resolve this problem. i've just install the patch last night and will look out for any improvement.

another thing, u might want to tweak the config parameter TCPKEEPALIVE and set it to TRUE.

cheers!
zarina 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Katie Lysons 
Date Jan 17, 2006 
Message Thanks Zarina.

I'll install the service packs this evening and ask further advice from our ESRI UK consultants re the TCP Keepalive - and let you know.

Katie Lysons 
  Katie Lysons

~~~~~~~~~~~~~~~~~~~~~~~~~
Geospatial Information Analyst
Network Rail, UK
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Leo Foretich Jr 
Date Jan 19, 2006 
Message What values are set in the following Oracle init parameters?

LICENSE_MAX_SESSIONS
SESSIONS


Also, there is also the possibility that your license manager isn't setup properly or isn't releasing connections. I think there was a similar problem here, and the solution was to stop and start the license manager. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author gulam irfani 
Date Jan 24, 2006 
Message If I face such a problem, I would write a script and run it as daemon and kill all those connections that are older than certain date and maintain a limit. If the scenario allows I would not make more than 100 connections. You can make this script smart enough to read a file that has list of connections which you dont want to kill. I have had experience that people don't disconnect for weeks. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Steve Bennett 
Date Jan 20, 2006 
Message Hi,

I have experienced this error a number of times myself.

As you mention, in the server_config is a parameter called CONNECTIONS. This sets the maximum number of SDE connections allowed against the database.

In your sde schema is a table called process_information. Every time you connect to an sde service a row gets created in this table (try it by connecting through ArcCatalog). This row is removed after you disconnect cleanly.

However if you don't disconnect the client cleanly (reboot the db without shutting down sde first or kill ArcMap in task manager) the row remains even though the connection no longer exists. Over time the number of rows in this table without a corresponding connection grow. Once the number of rows in the process_information table reaches the value of the CONNECTIONS dbtune parameter no new connections are allowed.

We've had the situation where we've had this error even though nobody is connected, because of the build up in this table.

To resolve it, either bounce the sde service or manually remove entries from the process_information table where there is no active connection. Obviously try and investigate why clients are getting killed off or not closing properly.

TCPKEEPALIVE may help, but doesn't stop this completely.

Steve Bennett
Ordnance Survey 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author z ma 
Date Jan 25, 2006 
Message hi,
thanks very much Steve for pointing out the sde.process_information table. i'll look into what's there.

i'm quite reluctant to clear a Shell script to kill off these processes, because some of these long (days) connections are quite valid due to my users work nature. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Steve Bennett 
Date Jan 27, 2006 
Message Just to be clear, I don't think you should kill off any processes, the problem is with rows in the process information table that shouldn't be there.

I guess you could write a script to check the sde.process_information table.

It could look for values in the server_id column. They should exist as processes (gsrvrs) on the sde server. If they do not exist as processes then remove the row from the table freeing up a connection.

This should be fine for application server (3 tier) mode, but more thought would be needed in a direct connect mode. The last thing you want to do is disconnect valid connections.

Usually I have found this table fills with entries where the connection and gsrvr process has long gone.

Add the check to your routine sde maintenance taks such as compression etc... 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Thomas Brown 
Date Jan 27, 2006 
Message Here's a script which can be used on Oracle to clean out the process_information table (execute as the SDE user) -

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;
/

This remove any orphaned entries in the table which can then prevent new connections (because of max connections).

Good luck. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Keith Shaw 
Date Jan 30, 2006 
Message You wouldn't happen to have this script for SQL Server would you TB? 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Thomas Brown 
Date Jan 30, 2006 
Message Unfortunately not.

The logic is as follows (applicable to SS2000) -

When SDE connects it sets the application name to SDE for the process. You can then query sysprocesses to know which connections are from SDE. SDE then uses the spid from SS as the unique id to identify the connection. This value is what you see in sde_process_information.

If during the insert into sde_process_information there is a unique constraint violation, we know the value of SPID is a duplicate (meaning it was orphaned and SS is recycling the spid value for the new connection). This statement detects the duplicate -

select count(*) from master.dbo.sysprocesses where spid = ? and program_name = 'SDE'

In respect to cleanup under lock collisions (in the object_locks, state_locks, table_locks, layer_locks) if there is ever a collision SDE verifies the current lock holder is still valid by checking to ensure the owner is still connected using the above query. If no rows are returned, then SDE knows the entry is invalid.

Based on this, you could then use the Oracle example which checks pipes to instead check the sysprocesses table for invalid entries and delete them.

If this seems to difficult, your best approach is to shutdown ArcSDE (make sure no users are connected with direct connect) and delete the entries manually.

Good luck. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author tony david 
Date Jun 25, 2006 
Message Hi there, am having a user with the same problem and error message. Specs:
1. Oracle 10g (Sun Solaris) running RAC
2. ArcSDE 9.1 SP1 (Direct Connect)
3. ArcGIS 9.1 SP1 and custom apps (Direct Connect with Oracle Client)

What is the best option to solve this.

1. Increase CONNECTIONS amount?? Does this really solve the problem because there is no actual cleanup of sessions in Direct Connect.

2. Cleanup the process_information table?? Do I just manually remove the entry in this table?? Is there any related table for this that I need to look at??

3. Does the script above work?? Am a bit worried the script seems to loop to other tables as well. Can't I just delete the entries in the process_information table and run the COMPRESS DATABASE tool to clean the states??

Am not sure why this is happening.

Thanks. Any help is much appreciated. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author jerry cox 
Date Jul 25, 2006 
Message Fellow SDE Users,

I've found that the SQL script listed in the earlier post works great!!! We've got SDE 9.1 running on Oracle 10g and have SDE limited to 48 connections of all types, ie. ArcIMS 9.1, ArcCatalog 9.1, ArcMap 9.1, SDE command line, etc. I've found that the combination of users and SDE command line operations never disconnecting cleanly quickly leaves us with the dreaded "connections exceeded" message. From the previous post --
Solution 1) simply postpones the inevitable and is a non-solution as stated...
Solution 2) is more trouble than its worth. How would one determine which connections are "orphaned"???
Solution 3) works great!!!, and can be implemented as a Before/After Insert Trigger, using the row count to determine whether it should fire or not. When the row count is 1 or 2 less than the Max Connections setting, fire the trigger to clean things up. This will ensure that the Process_Information table does not hold data for orphaned connections. Of course one does need to determine what the actual number of needed connections really may be, and plan their resource allocation accordingly. For us, the magic number of 48 works just fine.

Have a great day,
Jerry W. Cox
GIS Analyst
Santa Clara Valley Water District 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Katie Lysons 
Date Jul 31, 2006 
Message I found that the SQL script listed earlier also resolved our problems. I'm now running this as a weekly script out of hours.
I'm a bit concerned as to why these ArcIMS connections aren't being released properly, though, as we are shutting down the services and connections in the correct way (and as advised by ESRI UK, who are a bit flummoxed by this). Has anyone thought that there may possibly be a bug in the way that ArcIMS 9.1 drops its SDE connections?
Katie
 
  Katie Lysons

~~~~~~~~~~~~~~~~~~~~~~~~~
Geospatial Information Analyst
Network Rail, UK
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author xiannian chen 
Date Aug 01, 2006 
Message 1. Increase CONNECTIONS amount will not help. First, every connection takes resources and how many connections you want to set. It's not easy to be satisfied. Second, the direct connection still create the entry in PROCESS_INFORMATION table. It's true there is no GSRVR involved.

2. Cleaning up PROCESS_INFORMATION table could be a solutioni. But how do you know which connection is inactive and you can't delelte every connection in the table which might be active. If you know the dead connections, you need to remove information in the related tables as that script pointed out.

3. I think it should work. Havn't tried it yet. Looks great.
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author xiannian chen 
Date Aug 01, 2006 
Message This post is very helpful.

I don't think we need to remove all of the records in Process_Information table. As a matter of fact, we need to remove the inactive connections.

So, my problem is: how can we know which connection is inactive?

Thanks,
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Steve Tomlins 
Date Aug 02, 2006 
Message The SQL script above does that for you.

If you want to do it manually (less desirable) then use:

sdemon -o info -I users (see who is connected)
sdemon -o info -I locks (see who has locks)

In Oracle run :

a. SQL> select sid, process, substr(username,1,20) as username, status, substr(machine,1,20) as machine, substr(program,1,16) as program from v$session where program in ('gsrvr.exe', 'giomgr.exe');


b. SQL> select sde_id,server_id,start_time,substr(owner,1,20) as owner,direct_connect from sde.process_information;

c. SQL>select * from sde.table_locks

d. SQL>select * from sde.layer_locks

e. SQL>select * from sde.object_locks

f. SQL>select * from sde.state_locks

g. SQL>select a.owner||'.'||a.table_name locked_table,a.registration_id,
b.owner locked_by, b.start_time from sde.table_registry a, sde.process_information b, sde.table_locks c where a.registration_id = c.registration_id and b.sde_id = c.sde_id;

There should be enough info in all of this for you to find active connections.

Still the Code provided from TB should do what you need.

-Steve 
  Steve Tomlins
Answers4GIS.com 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author tony david 
Date Sep 25, 2006 
Message Hey... the script works... but make sure no one else is connected. Its a safety mechanism for ArcSDE and I traced the error to a coding problem in ArcGIS Engine... not ArcGIS DEsktop. 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1 max number connections 
Author Katie Lysons 
Date Sep 26, 2006 
Message Tony,
We've run this whilst many users are connected - it doesn't cause any problems. And we don't use ArcGIS Engine.
I think there are some known bugs at 9.1 which ESRI Inc are working to currently release hot-fixes for.
Cheers
Katie 
  Katie Lysons

~~~~~~~~~~~~~~~~~~~~~~~~~
Geospatial Information Analyst
Network Rail, UK
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1/9.2 max number connections 
Author Richard Daniels 
Date Mar 30, 2007 
Message Note that the name of the table on MS SQL will often be sde.SDE_Process_Information not sde.Process_Information.

Here is the code in Microsoft SQL for running the same comparision as previously shown for Oracle.

Note the simple way to get a count of current 'active', or at least SDE thinks they are active, connections would be

SELECT COUNT(*) AS ConnectCount FROM sde.SDE_process_information 
 
REM GET A LIST OF INACTIVE SDE CONNECTIONS

SELECT     sde_id, spid, server_id, owner, nodename, sysname, start_time, direct_connect
FROM         sde.SDE_process_information
WHERE     (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_state_locks)) AND (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_table_locks)) AND (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_layer_locks)) AND (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_object_locks))
 
  Richard Daniels 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1/9.2 max number connections 
Author A.J. Romanelli 
Date May 31, 2007 
Message Note - just because an sde process does not have any locks does not imply that it is not active (which is what the above SQL statement implies). 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1/9.2 max number connections 
Author Bill Rose 
Date Jan 11, 2008 
Message I see that the MS SQL query above (the second longer one) returns exactly those processes that I would expect need to be killed. Can anyone post an entire SQL script like the one provided earlier for Oracle?

For example, A.J., is there any reason not to do something like the following?
 
 
delete from sde.sde_process_information where sde_id in (
SELECT     sde_id
FROM         sde.SDE_process_information
WHERE     (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_state_locks)) AND (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_table_locks)) AND (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_layer_locks)) AND (sde_id NOT IN
                          (SELECT     sde_id
                            FROM          sde.SDE_object_locks))
)
 
  Bill Rose
rosewl@ci.richmond.va.us
City of Richmond, Virginia 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1/9.2 max number connections 
Author Bill Rose 
Date Jan 11, 2008 
Message In partial answer to my own question, I see that some operations such as simply opening an sde connection to the database without querying a table do result in a process record that appears in the query above. So A.J.'s observation is correct.

Still, this might not be a bad assumption to make since an open connection without any locks probably isn't doing much. This is likely to be the result of either an abnormal termination or a client who has walked away from their desktop.

Can anyone think of any reason not to schedule the modified SQL in the post above, assuming we are not performing long geo-processing operations that could be interrupted?
 
  Bill Rose
rosewl@ci.richmond.va.us
City of Richmond, Virginia 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1/9.2 max number connections 
Author Bill Rose 
Date Jan 14, 2008 
Message One more update on this: this might seem obvious, but removing the records from SDE_process_information does not remove the SQL Processes. The sdemon utility will report the process has been removed, but it remains at the RDBMS level. 
  Bill Rose
rosewl@ci.richmond.va.us
City of Richmond, Virginia 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: ArcSDE 9.1/9.2 max number connections 
Author Aaron Andrus 
Date Jun 10, 2008 
Message I'm no dba but I think this might be on the right track for Microsoft SQL Server. I don't see anything else in sysprocesses or sys.dm_tran_locks that would better report the status of spid than the status column in master.dbo.sysprocesses. If anyone else knows of a better way feel free to chime in. I haven't broken anything using this... yet. 
 
BEGIN TRAN tran_clean

DECLARE curs_spid CURSOR FOR 
SELECT sde_id, spid FROM sde_process_information
FOR READ ONLY

DECLARE @spid int
DECLARE @sdeid int
OPEN curs_spid
FETCH NEXT FROM curs_spid INTO @sdeid, @spid
IF (@@fetch_status = -1)
BEGIN
	PRINT 'No records found'
	CLOSE curs_spid
	DEALLOCATE curs_spid
	RETURN
END
WHILE (@@fetch_status = 0)
BEGIN

	DECLARE @lockstatus varchar(50)
	SET @lockstatus = (SELECT [status] FROM master.dbo.sysprocesses WHERE spid = @spid)

	IF (@lockstatus = 'sleeping')
	BEGIN
		DELETE FROM sde_process_information WHERE sde_id = @sdeid
		DELETE FROM sde_table_locks WHERE sde_id = @sdeid
		DELETE FROM sde_object_locks WHERE sde_id = @sdeid
		DELETE FROM sde_layer_locks WHERE sde_id = @sdeid
	END
	FETCH NEXT FROM curs_spid INTO @sdeid, @spid
END
CLOSE curs_spid
DEALLOCATE curs_spid

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); 

IF(@@error <> 0)
BEGIN
	ROLLBACK TRAN tran_clean
END
COMMIT TRAN