Dealing with ArcSDE connection limit exceeded?

Question

We routinely exceed ArcSDE's connection limit, about once a week. Many of the "connections" are nothing but orphaned records in the SDE.PROCESS_INFORMATION table. Is there any way to clean up these connections periodically? I just service packed ArcSDE to 9.3.1 SP2, hoping this would resolve the problem. (It did not.) Currently I restart the arcsde service to clean up the connections, a pretty inelegant solution.

This is the error, by the way:

Failed to connect to database. Maximum number of connections to instance exceeded

My workaround:

My solution was simple: restart the SDE service weekly, and double the number of allowed connections. This is of course not a solution; shame on ESRI for shipping enterprise software that requires such a workaround. After all, it's still possible to exceed the maximum connections; I can only hope that our current level of use doesn't lead to this.

I altered the init.d script to prevent sdemon from prompting the user to confirm they want to stop the service (this is what the -N argument does). I added a restart option as well.

#!/bin/bash
#
# arcsde Init file for starting and stopping ArcSDE 9.3
#
# chkconfig: 35 90 40
# description: ArcSDE startup script

# Source function library.

. /etc/rc.d/init.d/functions

SDE_OWNER="sde"
SDEHOME="/home/sde/sdeexe93"

case "$1" in
start)
echo -n $"Starting ArcSDE:"
su - $SDE_OWNER -c "$SDEHOME/bin/sdemon -o start -p pwd"
echo "OK"
;;
stop)
echo -n $"Stopping ArcSDE:"
su - $SDE_OWNER -c "$SDEHOME/bin/sdemon -o shutdown -p pwd -N"
echo "OK"
;;
restart)
cd "$CWD"
$0 stop
sleep 5
$0 start
;;
*)
echo $"Usage: $0 {start|stop|restart}"
esac

Then I created a cron job to restart the service once weekly:

# restart arcsde once weekly at 5am Saturdays
0 5 * * 6 service arcsde restart

I also increased the maximum number of allowed connections from 64 to 128 by editing the $SDEHOME/etc/giomgr.defs file:

CONNECTIONS      128     # maximum number of connections
                         # NOTE:  On windows machines, you may need to
                         # increase server non-interactive desktop memory.
                         # Consult the ESRI support site for more information.

Then I imported the new settings:

$ sdeconfig -o import -f $SDEHOME/etc/giomgr.defs -i esri_sde -u sde

That was it. We'll see how it goes.

Answer 1

There's an esri knowledge base article that talks about how orphaned connections are SUPPOSED to be handled. Of course, as others have pointed out, the simple (but not necessarily elegant) solution is to just restart your service.

Answer 2

You might also consider having your users direct connect and bypass the service altogether. This cleared up some issues I was having with services getting hung and resulted in some slight increase connection speed for users.

Answer 3

This is an answer that was sent from my supervisor to a client yesterday on this issue.

To increase the number of ArcSDE connections the following type of syntax can be used on the ArcSDE server:

sdeconfig -o alter -v CONNECTIONS=140 -u sde -p sde_password -i sde_instance

where "sde_instance" is the ArcSDE service name or port number.

You can verify that this change has been made using the command:

sdeconfig -o list -u sde -p sde_password -i sde_instance.*

Answer 4

We always just bounced the server periodically to do this...

Answer 5

Have you read this post on the ESRI Forum it's bit old but there are scripts that does what you want to do?