Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
How have you verified that the entries in the process_information tables are indeed orphaned connections? –  Derek Swingley Sep 21 '10 at 15:00
    
They are not all orphaned, of course, but I have found 2-week-old connections from workstations with uptime < 24 hours. –  nw1 Sep 21 '10 at 15:41
    
One thing I've been told: if orphaned connections exist, the process to clean them up isn't kicked off until there's a collision. Meaning, if you've got max connections set to 100 and there are 50 entries in process_info and let's say 20 of which are orphaned, connections won't be cleaned up until SDE tries to put that 101st entry in process_info. Anyway, you're saying your'e actually getting the max connections exceeded message so that process to clean up connections should be getting triggered. I'd investigate how people using those workstations are disconnecting. –  Derek Swingley Sep 21 '10 at 16:31
add comment

5 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
I did run the script posted by T B once, but it had no effect. It's almost 5 years old, so I suspect too much has changed in the environment since then. –  nw1 Sep 21 '10 at 15:37
    
I wouldn't assume too much has changed on the SDE side. Got a link to the script you ran? –  Derek Swingley Sep 21 '10 at 16:33
    
I don't think there should be any big differences between 9.2 and 9.3.1, ESRI changed the way geodatabases are handled but that's in ArcGIS 10. We are restarting our ArcSDE as a lot of others are suggesting, so I can't share any experience on using the scripts. –  MathiasWestin Sep 21 '10 at 17:00
    
the GDB stuff changed (tables with GDB prefixes were consolidated), but I'm pretty sure the underlying SDE stuff is the same. –  Derek Swingley Sep 21 '10 at 18:18
    
@Swingley I totaly agree with you. –  MathiasWestin Sep 21 '10 at 18:40
add comment

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

share|improve this answer
add comment

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.*

share|improve this answer
    
Yes, in fact I did this in addition to creating a cron job that restarts the arcsde service once weekly. –  nw1 Sep 23 '10 at 12:26
add comment

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.

share|improve this answer
    
Yes, and this also reduces the load on the machine; since the users PC handles the DAL keeping SQL just as a datastore versus having it do more programmatic support of the data. –  D.E.Wright May 6 '11 at 16:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.