ArcSDE Database Connections Not Closing

Question

I'm experiencing an issue where ArcSDE is not closing its connections to the database.  Only a single user is currently utilizing this SDE instance to connect to an Oracle 11g database through ArcCatalog.  When they disconnect a connection in ArcCatalog or even completely close ArcCatalog, SDE does not actually release the connections that had been opened.  This build up of stranded connections is seen by looking at the SDE.PROCESS_INFORMATION table.

Eventually the maximum number of connections is hit (the default, 48), and they receive a "Failed to connect to database.  Maximum number of connections to instance exceeded" error when they try to connect to a schema.  To allow connections to be made again, I have to actually restart the SDE service to clear out the stranded connections.  I�??m currently having to do this every few days.

Is there any reason why this connection build up might be occurring?  Thanks in advance for any assistance.

JimW1 · Answer

Enabling TCPKEEPALIVE would probably address the firewall-terminated sessions, though it's
probably worth the effort to address the brief connection firewall issue.

- V

Thanks Vince. This is great.

Ultimately a SDE restart schedule will have to made. This issue only cropped up as our non-GIS DBA addressed our table lock issues through scripting (rather than a restart) and hopefully the TCKEEPALIVE will help the other issue. It's always a challenge when GIS is not under corporate IS/IT.

VinceAngelo · Answer

Enabling TCPKEEPALIVE would probably address the firewall-terminated sessions, though it's
probably worth the effort to address the brief connection firewall issue.
 
- V

JimW1 · Answer

Is there any reason why this connection build up might be occurring?  Thanks in advance for any assistance.

We just went through this learning exercise yesterday here. Apparently there is a bevy of reasons a instance will hang.

For us, our corporate firewall closes the connection to SQL Server (ArcSDE) if the connection is inactive for more than 20 minutes. This will then hang the instance in SDE and report that a connection is still active. The only true solution/workaround has been to restart SDE.

Crashes will also hang a connection. Hard reboots. Power Outages. Etc.

This has been a known issue since 9.2 and is still happening in 10. Unfortunately it is our system security and there's not much we can do about it.

I think bumping up the connections to whatever your system can handle would hopefully carry you over until the next time you restart SDE. That's what we are counting on here - we had 3 weeks of up time on SDE and only then did this problem surface.