Kill only old connections

[BrentHoskisson]

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

[JakeSkinner]

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?

[BrentHoskisson]

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.

[JakeSkinner]

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)

[BrentHoskisson]

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

[MatthewRantala]

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.

[danan]

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.

[RayChilcote]

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

[BrentHoskisson]

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

[JakeSkinner]

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.