Program06

Programming, Auditing, Smiling
See also: Other Geeks@INDC

Found the new problem; maximum connection pool

This week, I face the *very annoying* red message on my web application; ….the maximum connection pool size was reached….

What I've done to resolve that problem is to investigate and to discuss with the developers who responsible for coding and building that application. We found that there is no connection leakage; every connection has been close and dispose, we don't use dataset which is not suitable for web application, and we put only one connection in a function (open and close the connection in each function).

Since we've almost been desperate to solve the problem, I found –during googling-- that there is an attribute called "pooling" that we can use in connection string. Its value can be either true or false. We agree to use the "pooling" attribute in the connectionstring tag in web.config file and we assign "Pooling = false:;. The result is "the *very annoying* red message telling the maximum connection pool doesn't show anymore".

However, I think that the solution is not so elegant. The HB (Hajar Bleh) Style still exists the in my development environment.. ;-) ting!

Should you have any suggestion to solve that problem, tell me, please?

Cheers,

Pingky

Share this post: | | | |
Posted: Dec 06 2008, 11:35 PM by Pingky Dezar | with 7 comment(s)

Comments

yulian said:

Try to check the Max Pool Size and Min Pool Size attribute. You can set those attributes to the higher value. The default value is 0 for the Min Pool Size, and 100 for the Max Pool Size...

I think it's enough if you use the default value for the Max Pool Size, hmmm...i guess there's something wrong with your code...try to detect the user connection from performance counter tools, add counter from SQL Server : General Statistics, then choose User Connections counter...

Maybe u can increase the Min Pool Size value to the appropriate number...the false value for Pooling atribute could consume the server's resource higher.

The different security context and different connection string in your connection object will create the difference connection that pooled.

# December 7, 2008 11:43 AM

Pingky Dezar said:

About max pool size, we decided to leave it at its max value; ie: 100 for Max Pool Size. I agree with you that there is something about Mary...ups.. I mean there is something about the code. That's why I *interrogate* the developers.  ;-) ting!

Anyway, as long as the "pooling=false" still work well, I don't want to change it. However, we will try the following tasks next week;

- Profiling the SQL Server. I hope I could find the *always open* connection

- Using Performance Counter Tools as your suggestion.

Thanks....

# December 7, 2008 12:44 PM

irwansyah said:

Bisa dikasih liat code untuk open dan close connectionnya? Karena asp.net adalah multithreaded environtment so ada kemungkinan function untuk close connectionnya ga thread safe jadinya ada connection yang masih menggantung dan terjadi berulangkali sampai max poolnya habis.

# December 7, 2008 5:33 PM

Pingky Dezar said:

Code untuk buka tutupnya nggak spesial sih. Kita punya aturan untuk buka tutup connection hanya ada di DALClass. Formatnya adalah sebagai berikut:

Dim semua yang diperlukan

Try

    conn.Open()

    ....

    result = OutputObject

Catch ex as Exception

    result = Convert ex.message ke OutputObject

Finally

    conn.Close()

    conn.Dispose()

End Try

Return result

OutputObject hanya berupa object bertype: Collection, String, Integer, ataupun Boolean.

# December 8, 2008 9:20 PM

cipto said:

Oh this is because you does not dispose and close your connection

# December 9, 2008 11:51 AM

Pingky Dezar said:

@Cipto

Have you read the previous comment?

We always close and dispose the connection in every function call. We have Try-Catch-Finally Block and put the close-dispose in the Finally part.

Could you tell me more specific of your *intuition* related the connection pool, please?

[Sometime we need intuition to detect the defect]  ;-)

# December 9, 2008 1:27 PM

cipto said:

o sorry about that, i read it more carefully now, i look it in a glance.

i bet it's because of the connection is not properly closed. or you create more and more new connection object.

can you make your connection a single ton object,if it's null create new one, If there's exist return the private holder connection object.

can not work? gc.collect

dispose command object?

hope this help

# December 9, 2008 4:08 PM