topic Re: Kill only old connections in Data Management Questions

Kill only old connections

BrentHoskisson — Mon, 02 Apr 2012 14:52:51 GMT

Is there a way to kill only the SDE connections that are over 24 hours old?

Re: Kill only old connections

JakeSkinner — Mon, 02 Apr 2012 15:59:55 GMT

Hi Brent,

You can use the 'sdemon -o kill' command and specify the PID for the connection that is greater than 24 hours. The 'sdemon -o info -I users' command will list each user connected, the PID, and the start time of the connection.

Or, are you looking for a more automated approach to do this?

Re: Kill only old connections

BrentHoskisson — Mon, 02 Apr 2012 16:02:42 GMT

The second one. It would be nice if I could give a list of PID's instead of having to do each one one at a time.

Re: Kill only old connections

JakeSkinner — Mon, 02 Apr 2012 17:45:51 GMT

It may be best to delete the entries from the 'sde.sde_process_information' table. What database are using (Oracle, SQL Server, PostgreSQL,...)?

Here's an example query on how to do this using SQL Server:

delete from sde.SDE_process_information where start_time < (select getdate() - 1)

Re: Kill only old connections

BrentHoskisson — Mon, 02 Apr 2012 17:52:26 GMT

That's what I was looking for. I didn't know it was stored in an sde table.

Re: Kill only old connections

MatthewRantala — Tue, 03 Apr 2012 11:15:08 GMT

It may be best to delete the entries from the 'sde.sde_process_information' table. What database are using (Oracle, SQL Server, PostgreSQL,...)?

Thanks for the tip, Jake. I've been familiar with this table but didn't know if it was safe/acceptable to just delete rows for old connections or if I needed to go the sdemon -o kill route.

Re: Kill only old connections

danan — Sat, 11 Dec 2021 22:32:47 GMT

Thanks for the tip, Jake. I've been familiar with this table but didn't know if it was safe/acceptable to just delete rows for old connections or if I needed to go the sdemon -o kill route.

What will happen if we delete an orphaned Direct Connect record in SDE.PROCESS_INFORMATION (Oracle 11gR2, ArcSDE 9.3.1 SP2)? Standard methods following an SDE_ID to a SERVER_ID to an OS process (RHEL 5) won't work because the server process is long gone. Various SDE lock tables reference the old SDE_ID. We don't have an ArcSDE application service so "sdemon" isn't likely to help. Again, there is no associated Oracle process running on the OS.

Is there a delete trigger for SDE.PROCESS_INFORMATION that will gracefully clean-up child records in various lock tables? Don't want to corrupt the ArcSDE repository. Or is bouncing the database a safe way to prune SDE.PROCESS_INFORMATION of orphaned connections when one's not running "sdemon."?

Or would the following code, posted in 2005, still be valid and safe to run against an active ArcSDE 9.3.1 SP2 Oracle instance. Code taken from the following post:
http://forums.esri.com/Thread.asp?c=2&f=59&t=174046

You can execute the following cleanup procedure to purge those rows if it is a concern (but the software will do it when it needs to anyhow) so you do NOT need to shutdown your ArcSDE instance - 

set SERVEROUTPUT ON 

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; 

DELETE FROM sde.process_information WHERE sde_id NOT IN (SELECT sde_id FROM sde.process_information); 
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 script is also beneficial for cleaning out the process_inforamation table.

Re: Kill only old connections

RayChilcote — Tue, 03 Apr 2012 15:18:58 GMT

I've found that many of these orphans are created due to an ungraceful exit (bugs, application errors, ArcMap, ArcCatalog, ArcObjects, geoprocessor, network/power outage, etc).

What I've found, first in personal geodatabases and later in SDE, is that the lock cannot be released until the owner (user on same machine) goes into the object and cleanly exits. (I'm not saying the other methods suggested don't work... trying to shed some light on cause and effect).

Performed the same workaround for SDE with such an orphaned lock.

So, logged in via the same citrix virtual machine (took a while to get the correct one, as they are randomly selected!), connected as the appropriate user, then drilled down to one of the locked FCs in the dataset (all FCs within that DS were locked).

Exited ArcCatalog (this is the 'clean' or 'graceful' exit).

Lock was released.

Not sure this helps shed some light on how and what is actually being cleaned up (rows, sid/session/serial ids, etc).

Look forward to trying the sdemon kills and/or the appropriate row removal next time around.

Thanks

Re: Kill only old connections

BrentHoskisson — Wed, 11 Apr 2012 11:39:18 GMT

Unfortunately I had to remove the "answered". While you can remove the database reference to the user and remove him from the list of "Sdemon -o info -I users". Deleting the line from the database does NOT remove the GSRVR.EXE that the user is using. Eventually you get enough of them and SDE refuses any more connections -- Even if it says you only have 12 users on the instance.

Sorry Jake, but your solution doesn't work.
Brent

Re: Kill only old connections

JakeSkinner — Mon, 16 Apr 2012 10:02:35 GMT

Unfortunately I had to remove the "answered". While you can remove the database reference to the user and remove him from the list of "Sdemon -o info -I users". Deleting the line from the database does NOT remove the GSRVR.EXE that the user is using. Eventually you get enough of them and SDE refuses any more connections -- Even if it says you only have 12 users on the instance.

Sorry Jake, but your solution doesn't work.
Brent

Brent,

It looks like you are connecting to you SDE geodatabase using an ArcSDE service. Have you considered using a direct connection? This is usually the recommended way to connect an SDE geodatabase. Advantage:

The direct-connect architecture moves ArcSDE functionality to the desktop. This removes the ArcSDE load from the server and allows additional resources to be freed up for the DBMS, which means you get better scalability on the database server.Direct connections can be faster if the server is heavily used, because processing takes place on the client machine. Rather than having to process the information on the server, which may be responding to requests from numerous other users at the same time, and send the information back to the client across the network, the client computer is typically only processing a few tasks at a time.

Direct Connections will also not spawn any GSRVRs.

If you must use an ArcSDE service to connect to the geodatabase you should set the TCPKEEPALIVE parameter to TRUE. Doing so will clean up any orphaned GSRVR processes. Please see the following KB article.

Re: Kill only old connections

BrentHoskisson — Mon, 16 Apr 2012 12:51:57 GMT

Brent,

It looks like you are connecting to you SDE geodatabase using an ArcSDE service. Have you considered using a direct connection? This is usually the recommended way to connect an SDE geodatabase.

This is an entirely different complaint. But my users are only using ArcEngine Runtime. Direct Connect doesn't work (apparently) except with ArcView or better. So yes, I have tried it - doesn't work - oh well, I'm dealing with it.

Re: Kill only old connections

JakeSkinner — Tue, 17 Apr 2012 13:10:29 GMT

I would follow the KB article then, and set your TCPKEEPALIVE to TRUE. Remember to restart the ArcSDE service for the changes to take affect.

Re: Kill only old connections

JamesLandwehr — Fri, 31 Oct 2014 15:01:27 GMT

Jake, does this mean that if we are using direct connects that your method of deleting old/orphan SID/SPID's at the SQL level is okay? We stop and restart our services every night and from what I can see of the processes, we have a bunch of orphans from yesterday out there in addition to the ones that started this morning. Will running this command clear those out? Thanks.