|
Subject:
|
Find Open DB Connections?
|
|
Posted By:
|
Ron Howerton
|
Post Date:
|
4/18/2008 9:39:07 AM
|
I am randomly getting this error every few days:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I don't wish to increase pool size or turning off pooling.
I just want to figure out which connection(s) are being left open. There is a lot of code to be checked, so randomly searching for this problem is unlikely to find the problem, and a trace could potentially turn up hundreds of DB calls that would be as painful to search as the code itself. Are there any tools or techniques that can help me find where in my code connections are being left open.
|
|
Reply By:
|
humour
|
Reply Date:
|
4/20/2008 9:40:27 PM
|
I realize this won't help a lot now but I have adopted a technique wherein all DB interactions by my coding practice occur via Class Functions. Within the Function I allways dispose of my connection objects. Disposed connection objects should immediately release resources to the connection-pool.
I have tried asking on other forums if this is a good technique or a bad technique.... (perhaps it has a cost penalty in performance - I really don't know).
In any case I have provided an example below.
Dim ss As String
ss = Me.GetConnectionString(connectname)
Dim sqlconn As New SqlConnection(GetConnectionString(connectname))
Dim SQLComm As New SqlCommand(sql, sqlconn)
sqlconn.Open()
SQLComm.ExecuteNonQuery()
sqlconn.Close()
sqlconn.Dispose()
SQLComm.Dispose()
|
|
Reply By:
|
Ron Howerton
|
Reply Date:
|
4/23/2008 10:21:54 AM
|
Thanks for the advice, but I am trying to do the same thing.
I will repost the question now lest anybody think I got a useful response.
|
|
Reply By:
|
gbianchi
|
Reply Date:
|
4/23/2008 10:35:12 AM
|
Hi there... the only real advice, is to control every db open and close it when you don't needed anymore..
Also, if you have hundreds of DB open, that is not a good quality code, maybe you have to refactor it??
Humour idea is not bad, at least you will trace your code better.
HTH
Gonzalo
===========================================================
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
===========================================================
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
===========================================================
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles/Classics-Week-I-Hate-You.aspx
===========================================================
|
|
Reply By:
|
Ron Howerton
|
Reply Date:
|
4/23/2008 11:27:20 AM
|
You understanding of the problem is at best incomplete and, in any case, I do not have the luxury of rewriting this code. I simply asked for a technique for identifying where one leak is occuring. If you can answer that question, I would be most interested in your response. But advice to rewrite a 100K line system to prevent one open DB connection is not helpful.
|
|
Reply By:
|
dparsons
|
Reply Date:
|
4/23/2008 11:51:10 AM
|
Without going back and tracing your code (and I understand that it would be a rather large undertaking for 100K lines of code) you are probably going to have to resort to doing this from the SQL side.
In SQL Server 2K you have the system proc sp_who 'username' which will give you information about the user, the last executed command etc. (Most of this information can also be seen through Enterprise Manager) Pay close attention to the last command the user executed as this will at least "get you in the ballpark" of where the connection leak may be coming from.
Further, if you are using Sql Server 2K5 you can also use sp_who2 which contains some more detailed information about the connection.
-Doug
===========================================================
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
===========================================================
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
===========================================================
|
|
Reply By:
|
Ron Howerton
|
Reply Date:
|
4/23/2008 12:29:04 PM
|
Thanks, Doug!
Looking at it right now, all of the SPIDS are SLEEPING and there appear to be fewer SPIDS than pooled connections I allowed for in the connection string. Is there some way to identify opened connections or is this only useful once the error manifests?
|
