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.

How have you verified that the entries in the process_information tables are indeed orphaned connections?
–
Derek SwingleySep 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.
–
nw1Sep 21 '10 at 15:41

1

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 SwingleySep 21 '10 at 16:31

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.

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.WrightMay 6 '11 at 16:24

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.
–
nw1Sep 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 SwingleySep 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.
–
MathiasWestinSep 21 '10 at 17:00