Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts

Sunday, March 25, 2012

Connection pools constantly grow

I've got a little console app that basically pulls back a recordset from our SQL Server 2005, goes through each row in the dataset and may/may not insert a record into a different table in the database. We use sproc's for every transaction and I close every connection in the application. However, when the application ends, I still show connection pools open in the performance monitor. Same with websites that I know have no traffic or that have been stopped by me in IIS.

Last night I showed a total of 6000+ "Current # pooled and nonpooled connections". Should I be worried about what seems to be unending growth in the connection pools? If so, how can I look to manage this better?

Check the following article:

http://www.15seconds.com/Issue/040830.htm

Regards

|||

I actually read that article yesterday.

Perhaps I'm dealing with a connection leak. If so, I don't know how. All my transactions are either handled via:

Try SqlConnection.Open() SqlCommand.ExecuteNonQuery()Finally SqlConnection.Close()End Try

Or

Try SqlDataAdapter.Fill(DataSet,"Results")
Finally
If SqlConnection.State = ConnectionState.OpenThen SqlConnection.Close()End If
End Try

My program uses two databases, which means that there are two connections strings. That accounts for the reason the program creates 2 connection pools when it runs, but why aren't those pools closed when the program finishes? Next time the program runs, it's increased by 2 again.

sqlsql

Thursday, March 22, 2012

Connection pooling & transaction context

I have been using connection pooling in a midle tier application with a very
good result. But I've always wondered what it means - when several articles
mention that the provider will divide the pool into subpools so to speak
based on transaction context among other things.
What exactly does this mean - transaction context? After all, under normal
circumstances there should be no active transaction when you close the
connection - leaving only one kind of transaction context in the pool.
Thanks in advance for any comments.
I see you haven't gotten a response on this. If you have a component based
application - like a COM+ application where multiple components in the same
transaction may open database connections, each component will open a
connection and enlist the connection in the transaction. If a connection is
already enlisted in the transaction from an earlier component running in the
same transaction it can be used without going through the enlistment
process. This saves the enlistment time and also makes commit faster
because there are fewer resource managers enlisted in the transaction.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"samse" <samse@.discussions.microsoft.com> wrote in message
news:E468BEFF-D5CC-4779-AEE0-3E0CE60F76EB@.microsoft.com...
>I have been using connection pooling in a midle tier application with a
>very
> good result. But I've always wondered what it means - when several
> articles
> mention that the provider will divide the pool into subpools so to speak
> based on transaction context among other things.
> What exactly does this mean - transaction context? After all, under normal
> circumstances there should be no active transaction when you close the
> connection - leaving only one kind of transaction context in the pool.
> Thanks in advance for any comments.

Monday, March 19, 2012

Connection issue with named instance

Hi all,
Since the replacement of new client workstation I've trouble for connection
of these new machines ot My different SQL Servers. On this configuration we
only speak of client/server connected on the same LAN without any antivirus
enabled.
When my users hae their new PC's (W2K - MDAC 2.6 SP1), they try to connect
to my server via for example ODBC and in this case when they browse network
they can't see named instance (only default instance is displayed (port 1433)
!). Next to it if they force the name in the connection string they still
can't connect ot the server, because of the port resolution. My workaround
today is to force the port in the connection string of each client (quite
heavy, 2500 workstations !!!) On my servers I have TCP/IP and Named Pipes
enable.
I've performed different test of connection (telnet 1433 work, PortQRY
return that 1433 and 1434 are working (but it didn't return all the instances
name), ping OK, tracert ok,...)
Any help would be appreciated, thanks in advance
Sylvain
Can you please follow below links and let us know if it was helpful for you.
http://sqljunkies.com/How%20To/2E110...85DFA439B.scuk
&
http://support.microsoft.com/default...b;en-us;287932
Regards
Surajit
"Sylvain" wrote:

> Hi all,
> Since the replacement of new client workstation I've trouble for connection
> of these new machines ot My different SQL Servers. On this configuration we
> only speak of client/server connected on the same LAN without any antivirus
> enabled.
> When my users hae their new PC's (W2K - MDAC 2.6 SP1), they try to connect
> to my server via for example ODBC and in this case when they browse network
> they can't see named instance (only default instance is displayed (port 1433)
> !). Next to it if they force the name in the connection string they still
> can't connect ot the server, because of the port resolution. My workaround
> today is to force the port in the connection string of each client (quite
> heavy, 2500 workstations !!!) On my servers I have TCP/IP and Named Pipes
> enable.
> I've performed different test of connection (telnet 1433 work, PortQRY
> return that 1433 and 1434 are working (but it didn't return all the instances
> name), ping OK, tracert ok,...)
> Any help would be appreciated, thanks in advance
> Sylvain
|||Thanks for your reply.
Concerning the first link, after checking all cases the result is the same
than before, the only workaround is to create an alias (force port number).
All my clients even set on dynamically determine port, can't find an instance
other than the default except if I set manually the expected port by the
server.
For the second link, after checking with my network team we have validate
that no firewall are placed between clients and servers.
Thanks for your help
Sylvain
"surajits" wrote:
[vbcol=seagreen]
> Can you please follow below links and let us know if it was helpful for you.
> http://sqljunkies.com/How%20To/2E110...85DFA439B.scuk
> &
> http://support.microsoft.com/default...b;en-us;287932
>
> Regards
> Surajit
>
>
> "Sylvain" wrote:

Sunday, March 11, 2012

connection help

hey im 2006 passed out.,im a trainee in asp platform.,

ive been provided windows authentication and i have a code showing error. could u please help me and i got this code from net only else u giv me a correct code to connect asp and sql2005

my code

<%

set con=server.CreateObject("ADODB.connection")

con.Open dsn="dsn_uma"

set rs=server.CreateObject("ADODB.recordset")

rs.Open "select * from xxtable",con,1,3

while(not rs.EOF)

for i=0 to rs.RecordCount

Response.Write rs(i) & ":"

next

Response.Write "<br>"

rs.MoveNext()

wend

%>

Please check this link http://www.frontpages-web-hosting.net/support/asp.access.database.connection.htm

Make sure your DSN is configured correctly and tell us the exact error msg if you still see errors.