0

I am a DBA, not a developer, and could use some insight. The development staff is using VB.NET to create web based applications with connections to a DB2 database. Please assume that the connection string in the web.config file is coded correctly.

I can see the number of orphaned connections from the web servers grow over time. By orphaned I mean that there is no activity associated with the connection for hours, yet I can see other connections being created and destroyed every couple of seconds.

I suspect that the connections are not being properly closed, but there are two different groups looking at the problem and so far haven't turned up anything. By the end of the day I can have hundreds of these connections - all of which are cleared when the application pool is reset every night. (This means it is NOT a database problem)

Is there a coding technique to ensure a connection is closed using vb.net on IIS v7+? Am I seeing the symptom of another problem on IIS?

Doug
  • 11
  • 1
  • 1
  • Any connections that are not released properly in the code will remain open until the user's [session times out](http://msdn.microsoft.com/en-us/library/system.web.sessionstate.httpsessionstate.timeout.aspx). People will become grouchy if you shorten that too much, but as a test you could shorten it a lot to demonstrate that there are abandoned open connections in the code that those pesky VB people need to root out. Everybody's session will likely get timed out when the app pool is reset (if they're using the default session approach). – DOK Jul 02 '13 at 16:18
  • In my experience, the other most frequent culprit is misuse of DataReaders (firehose cursors). Those must be manually [closed in code](http://msdn.microsoft.com/en-us/library/haa3afyz%28v=vs.110%29.aspx). – DOK Jul 02 '13 at 16:22
  • And finally, you might also find some expert guidance at the sister [DBA site](http://dba.stackexchange.com/) – DOK Jul 02 '13 at 16:24
  • Check with your developers and see if they're using `Using` blocks with the connections when they're doing database operations - the `Using` block will ensure the connection is closed and properly disposed. – Tim Jul 02 '13 at 16:47
  • Good points about the using blocks. However, in some situations the DataReader is returned to a calling method which reads the data and maps it into a custom object. In that case, you have to wait to close the DataReader later, after it has been read. This is often the source of connections left open, when the calling method reads the DataReader without closing it. – DOK Jul 02 '13 at 17:25

1 Answers1

0

You need to have the developers implement the Dispose pattern, which is facilitated by the Using statement in VB.NET, like this (this is pertinent to SQL Server, but whatever connection object you are using for DB2 should work as well):

Using theConnection As New SqlConnection()
    '' Command, parameter logic goes here

End Using

By wrapping the connection object inside of a Using block, it will guarantee that the connection is closed and properly disposed of memory-wise, even if there is an exception in the code within the Using block.

Sounds like a code-review is in order for whoever is in charge of the developers.

Karl Anderson
  • 34,606
  • 12
  • 65
  • 80
  • I am having the Developers research the issue and so far they have not found anything in the code. The search continues... – Doug Jul 10 '13 at 19:03