Connection Pooling.

I have created an instance of sqlconnection and using transaction i am executing 5 insert statements (Database - SQL Server 2005). I have the finally block where i call the dispose method of the transaction and close method of the connection object.I often get the below mentioned error:

Exception Type: System.InvalidOperationException

Message: 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.

TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)

Source: System.Data

How do i resolve the above issue?

do you a transaction.Commit()?

Put the code.

Regards.

PD:

this is a transaction example:

Code Snippet

using (dbcmd.Transaction = dbcon.BeginTransaction())

{

try

{

dbcmd.CommandText = string.Format(sql1, tablaDestino, sqlOrigen);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql2, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql3, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql4, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql5, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.Transaction.Commit();

}

catch (DbException dbException)

{

dbcmd.Transaction.Rollback();

throw new PoolException("La transaci￠n de creaci￠n de pool ha fallado","CreatePool", 0, dbException);

}

}

|||

Yes, I am doing a commit. We are using DAAB SQLHelper class.

|||

You may try to increase the size of the connection pool. For example: Set Min Pool Size=10;Max Pool Size=1000 in your connection string

|||I have increased the connection pool size. min as 5 and max 100. still it gives me a problem.|||I've had simillar problem with the connection pooling. Have you made sure that the connection used is closed after you are finished with it.

I found loads of open connections within my code and closing them seams to have helped.

Colin
|||Yes i am closing the connection after using.|||

I don't think this has anything to do with connection pooling once the connection is open -- as soon as you open your connection, it's yours until you close the connection -- no other connection can use it until you close it.

Connection pooling doesn't utilise your physical connection for other logical connections while it's open -- it simply doesn't physically close connections when you close them and assigns those connections to new logical connections by client applications when required.

|||Note that you simply may not have enough connections in the pool initially. By default, the MaxPoolSize=100. Try increasing this by placing MaxPoolSize=500 in the connection string.

Connection Pooling.

I have created an instance of sqlconnection and using transaction i am executing 5 insert statements (Database - SQL Server 2005). I have the finally block where i call the dispose method of the transaction and close method of the connection object.I often get the below mentioned error:

Exception Type: System.InvalidOperationException

Message: 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.

TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)

Source: System.Data

How do i resolve the above issue?

do you a transaction.Commit()?

Put the code.

Regards.

PD:

this is a transaction example:

Code Snippet

using (dbcmd.Transaction = dbcon.BeginTransaction())

{

try

{

dbcmd.CommandText = string.Format(sql1, tablaDestino, sqlOrigen);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql2, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql3, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql4, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.CommandText = string.Format(sql5, tablaDestino);

dbcmd.ExecuteNonQuery();

dbcmd.Transaction.Commit();

}

catch (DbException dbException)

{

dbcmd.Transaction.Rollback();

throw new PoolException("La transaci￠n de creaci￠n de pool ha fallado","CreatePool", 0, dbException);

}

}

|||

Yes, I am doing a commit. We are using DAAB SQLHelper class.

|||

You may try to increase the size of the connection pool. For example: Set Min Pool Size=10;Max Pool Size=1000 in your connection string

|||I have increased the connection pool size. min as 5 and max 100. still it gives me a problem.|||I've had simillar problem with the connection pooling. Have you made sure that the connection used is closed after you are finished with it.

I found loads of open connections within my code and closing them seams to have helped.

Colin
|||Yes i am closing the connection after using.|||

I don't think this has anything to do with connection pooling once the connection is open -- as soon as you open your connection, it's yours until you close the connection -- no other connection can use it until you close it.

Connection pooling doesn't utilise your physical connection for other logical connections while it's open -- it simply doesn't physically close connections when you close them and assigns those connections to new logical connections by client applications when required.

|||Note that you simply may not have enough connections in the pool initially. By default, the MaxPoolSize=100. Try increasing this by placing MaxPoolSize=500 in the connection string.

Connection Pooling Question

Hi,
I was asking in one Technical Test, the question is not exactly like,but i want post the outline of that(because i don't remember the exactone)
Suppose u have connection with Sql Server database, and let say some 30users are already connected (through some aspx page), if one more newrequest is coming, is it created new path or it will reallocate ordestroy existing connection poll and assign??
Can anybody tell me?
if possible please post some of Basic and Adavanced questions regarding Sql Server (with aspx)
Thanks
Jen

It will reallocate the connection depending on how many connections your web server is allowing. If it only allows 30 connections, and they are all being used, the 31st connection will error out. If you are using datasets, those connections are closed so they wont hold the connection open. If you are using a datareader, they are active connections, and will stay open until either you close them yourself (dr.close and oconn.close) or the garbage collector finally collects them.
Let me know if you have any other questions or this didnt help.
Nick

|||Hi
Thanks for help Nick.. Thats enough and i will try to fine some links to explore more about it.
Thanks

connection pooling problem w/ ASP.NET 2.0 and SQL 2005 64 bit?

Here's the gist of the problem I am having:
I just deployed my new ASP.NET 2.0 / C# web app to "production". I have a
web server running Windows 2003, all the latest updates, etc. As mentioned,
the web app is running under ASP.NET 2 and C3. On a separate server, I am
running SQL Server 2005 64 bit, Standard Edition (SP1).
Note, I have separate development and test environments that mimic the above
setup. That is I have a dev web server accessing a separate dev database
server; the only difference is that the db server is SQL 2005 32 bit, Std
Edition; same in test.
My production web app is experiencing connection pooling problems. If I
bring up the activity monitor on the production db server while I am
exercising some of the web pages, I can watch the connections grow to the
100 max (the default for asp.net) very quickly (just one person hitting a
page that exercises a database query) and then the .NET app will eventually
throw an error:
Message: 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.
Source: System.Data...
Some things to consider:
1) Before everyone jumps to the conclusion about datareaders, please bear in
mind that the EXACT same .NET code is on dev and test web servers; I am NOT
able to get the connections to max out on dev or test; whereas on production
web server, I can easily get it to max out. It's not a code issue. Yes, I
know all about closing DataReader connections explicitly, blah, blah. It's
not the code.
2) There are 2 major difference on the db server; however
a) the production server is 64 bit; my other db servers are 32 bit
b) I actually spent quite a bit of time today on my production db server in
an effort to get linked server stuff working. So, there are some definite
things I did on the prod server db that I have *not* done on dev or test sql
boxes, such as
- turning on MS DTC security config settings (in Component Services); there
are many MS blogs about this and the following MSKB:
http://support.microsoft.com/kb/899191
- enabling named pipes
So, I'm thinking I have 2 straws to grasp at:
A) call Microsoft tech support to see if there's anything that google hasn't
turned up
B) uninstall SQL Server 2005 on my production db server and reinstall using
a 32 bit version. Of course, since this is production. I bought 64 bit SQL,
so would I be "allowed" to downgrade to 32 bit? The only additional disc
media I have are MSDN discs.Hi,
Thanks for using Microsoft Managed Newsgroup.
From your description, I understand that:
Your ASP.NET 2.0 web application with SQL Server 2005 64 bit SP1 was
running on a production environment. The database server was on a separated
machine. You found that the connections grew quickly to reach the max
connection limit on your SQL Server and then any new connecion was timeout.
However no problem appeared on your test and development environment.
If I have misunderstood, please let me know.
For further research, I would like your answering me several questions:
1. How long had the SQL Server been normally running before the problem
appeared?
2. What are the situations of CPU and memory usage at that time?
3. Could you see some errors in the Event logs?
Also, I would like to collect the following information from you:
1. MPSReport:
Please help create a MPSReport on the problematic machine, which will
collect
ERRORLOG, Windows event log and other helpful information about the SQL
Server. To
create a MPSReport, please visit the following web site:
<http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F
-88B7-F9
C79B7306C0&displaylang=en>.
And look for MPSRPT_SQL.EXE. Download it and run it on the machine. Dismiss
the
prompted dialog boxes and the readme window (usually a Notepad) after you
read
them. After the Command Prompt window closes itself, collect the cab file
the tool
has generated and sent it to me.
2. SQL Error log:
By default, they are located at: C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG -- Send all the files under this folder with zip
format to me.
3. Server Configurations:
Use sqlcmd or osql to run the following batch and send the output file to
me:
Under command prompt: sqlcmd -S ServerName\InstanceName -E -o C:\output.txt
exec sp_configure 'show advanced', 1
reconfigure with override
exec sp_configure
go
4. SQL Trace file
Use SQL Profiler to monitor your SQL Server to see what are the SQLs or SPs
being executed at that time.
I will create a web file space for your uploading the large files. However
you may need to leave me your email so that I can mail you the password.
If it is not convenient for you to leave your email here, you can send an
email to me (changliw@.microsoft.com).
Note: Our managed newsgroup is focused on break/fix issues that are neither
urgent nor complex. If the issue is urgent to your business, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if you need further assistance on this issue.
We appreciate your posting back at your convenience and let us know the
issue status.
If you have any other questions or concerns, please feel free to let us
know. It is always our pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support

Connection pooling problem

Can anybody help me to solve this problem

I have a web application. In this app i have one class genral for database transections. I create new object of this class on every page in page load event & dispose it on UnLoad event. After sometime this app gives connection pooling exception.

Means connection are not closed, but i disposed the object like

tmpDatabase.Dispose()
tmpDatabase = Nothing

Plz can anybody tell me the mistake i m doing.

ThanxPersonally, I would contruct the object as late as possible (where needed) and destroy the object as soon as possible, and let connection pooling work as designed.

Perhaps there is som error condition that you are not handling, so that the dispose is sometimes not reached.|||I can see using Break point, it destroying the object. Means cursor reaching to the line where the object is destroyed.|||Please Help me

This is big problem for my application. I dont know what i doing wrong.|||"This is big problem for my application. I dont know what i doing wrong."

We do not either. In general, there is some reason why your connections are not being closed. Could be an error condition not properly handled, could be a design problem. Absent seeing much of the code, and absent you taking my advice to make the Open and Close very close to each other, leaving the connection open for as short a time as possible rather than creating one for a page load/unload cycle, I do not think anyone can help you.

Connection Pooling on Analysis Services

Please may I have your assistance with the following issue. Let me 1st describe the scenario. The front end uses BEA Web Logic which queries an IIS Web Service that in turns queries MS Analysis Services 2005.

The Web Service builds the MDX queries dynamically and returns the results in XML.

Each time we run a query we open a connection using ADOMD, fire the MDX to return the result set then close the connection. However we don't think this will provide the best performance. So

1. Does the AS OLEDB provider handle connection pooling internally?

2. If not, is it possible to implement connection pooling in code inside our web service?

3. Are there other ways to implement connection pooling? for example does using HTTP connections automatically provide us with connection pooling?

Thanks

Here is an article about implementing connection pooling in AS2000, the same should apply for AS2005.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_connpooling.asp

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Many thanks for the document. Please have a look at the following.

http://www.codecomments.com/archive364-2005-1-379959.html

What would be ideal is when the web service starts this will automatically create a fixed number of connection pools to an AS Db

Our objective is to be able to reuse these connection pools by different users connecting to the same web service, obviously not simultaneously. The above suggests that the connection pool is destroyed?

Any thoughts would be greatly appreciated

Thanks

John

|||

No.

The post you mention suggests only that you should apply a bit more logic beyond simple 3 step;

1. Application starts.
2. It creates pool of open connecitions size N.
3. Pool of connections is destroyed when you shut down your web service.

It suggests you implement logic in your application that allows your application to grow conneciton pool above pre-set limit N:
In beginning your conneciton pool is N , if application used all of the connections from the conneciton pool and still needs more connections, connection pool will grow to N +x ... Your application will periodically check if it needs to srink connection pool back to N.

As for the different users connecting to your application. You can re-use connections if application is using same credentials to retrevie data from Analysis Server.
For instance if user A and B connecting to your web service running under W credentials. If your connections are opened under W , you can re-use them . If you open connections under A , you should not let B re-use that conneciton , otherwize you might show B too much data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward, Many thanks for your tips and based on your recommendation (to pursue AS 2000) downloaded the MS XML FOR AS SDK (AS 2000) and started to look at the various options

We used the reference to this

Public Shared p As New MSXmlAnalysisSCLib.ADOConPool

p.MaxSessions = 5

cnadomd = p.GetConnection("Data Source=myASServer" & ";Provider=msolap.3; initial catalog=myASDb")

Unfortunately we dropped the above method because there are no options to use XML? This will mean we would need to read the cells and construct our own XML string

Then we looked at the option of using http connection using the latest AS 2005 connection provider which seems to be set up to use connection pooling

conn.ConnectionString = "Data Source=http://myWebServer/OLAPhttp/msmdpump.dll" & ";Provider=msolap.3; initial catalog=myASDb"

As the content of the msmdpump.ini file suggests ::

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>10</ConnectionPoolSize>

<MinThreadPoolSize>1</MinThreadPoolSize>

<MaxThreadPoolSize>10</MaxThreadPoolSize>

<MaxThreadsPerClient>5</MaxThreadsPerClient>

</ConfigurationSettings>

However from perfmon as well as SQL profiler, when issuing multiple connections simultaneously using the above http method it will use the same connection Id, but create multiple ‘current user sessions’. Also the observed execution time was rather slow.

The alternative is to use the following

conn.ConnectionString = "Data Source=myASServer;Provider=msolap.3; initial catalog=myASDb"

which is observed to work correctly when we use multiple connection simultaneously and performs well.

However the issue I have with the above is where do I instantiate the above so that I can re-use the connection. I noticed in IIS we can configure application pooling.

A low level example would be greatly appreciated, if you don’t mind?

Thanks

John

|||

First you can try and increase the ConnectionPoolSize in the msmdpump.ini .

Creating a single connection and having it be reused across several sessions should have comparable or better performance to opening new TCP connections all the time.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Connection pooling not working in Clustered Env.

Hi,
We had deployed a .NET application in a non clustered environment with conection pooling and was working fine. Now we had moved to a clustered env and connection pooling stopped working. Is there any cluster aware setting that we need to look at. Please let me know your thoughts.
Thanks in advanceCan you be more specific on what went wrong? Any error code, exception stack will be helpful.sqlsql

Connection Pooling Logic

We have our own home grown connection pooling method that is not too
resilient.
I want to know how you handle connections to a database i.e how may
connections do you open as your Web/App tier starts up to the database, what
is the max that you allow ?
Also in the event SQL Server is slow, how do you ensure that the Web/App
tier does not open more connections ?
We have been struggling with this today as our Web/App tier does not know
better when to back off or just open new connections if there is more load.
ThanksHi
You connection pool should open a new connection when there are no free
connections, therefore if the server is not responding well connections are
going to remain in use longer! If you define a maximum pool size this will
give you an upper limit rather than creating additional connections and
potentially exacerbating the problems. The application should handle the
error generated when new connections can not be created.
Alerting when the pool is at it's upper limit should inform you when the
system is having problems although other alerts on CPU/Memory used may be
triggered first.
John
"Hassan" wrote:
> We have our own home grown connection pooling method that is not too
> resilient.
> I want to know how you handle connections to a database i.e how may
> connections do you open as your Web/App tier starts up to the database, what
> is the max that you allow ?
> Also in the event SQL Server is slow, how do you ensure that the Web/App
> tier does not open more connections ?
> We have been struggling with this today as our Web/App tier does not know
> better when to back off or just open new connections if there is more load.
> Thanks
>
>

Connection Pooling Limit thru ODBC

Hello, Does anyone know if it's possible to limit the actual number of possible connections made to a database using the connection pooling services provided by odbc (let's assume odbc and not ole, as quite honestly i intend this for connecting to MSDE)? my understanding is that the connection limit provided with the pool strictly manages the number of connections within the pool versus the total number of connections.

Ideally I would like connection requests to be queued and offered when available.. which I would further like to specify in the code using the connection. what I mean by this is that I would like to explicitly flag the connection as unused (thereby skipping the wait for idle time), when I'm done with it.

why do I want to do such things? well, and here's where my problem may lie, I want to use MSDE (with it's reccomended 5 connection limit) on a single machine. Although there will only be a single user, there exist a whole slew of *different* apps that need to jump in, read/write data and leave again.

please excuse my ignorance if I'm attempting to get around this the wrong way. i look forward to a discussion.

abLook at SQL Server Properties - Connection TAB - Remote Server Connections.

[ ]'s|||Excellent, gotta love how simple that is! do you happen to know what happens when the max connections are used? is the connection request denied or simply qeued until a possible timeout? also do you know what i should do with a connection, once finished with it in a DLL for instance, to best take advantage of connection pooling? thanks

ab

Originally posted by PedroHenrique
Look at SQL Server Properties - Connection TAB - Remote Server Connections.

[ ]'s

Connection pooling issues!

I've got a function that returns a connection to a DataReader:

static public SqlDataReader GetDR(string sql,string connString){SqlConnection myConn =new SqlConnection(connString);SqlCommand cmd =new SqlCommand(sql, myConn);SqlDataReader dr;myConn.Open();dr = cmd.ExecuteReader();return dr;}

I call it like so:

SqlDataReader dr = PAS.GetSqlDataReader.GetDR(sql, connString);
And I'm always careful to close the connection when I'm done using it via dr.Close(). Except I just started noticing that when I run my app and
start doing a fair amount of work around it - I very quickly get a timeout that I've run out of connections. D'oh! I've triple checked my code and
verified that Ialways remember to close my connections. It's there -every single time!

So I'm obviously missing something. Should I not be using a function like GetDR since it doesn't close the connection within the function? 

closing a reader does not automatically close the connection.

dr.close() simply closes the reader leaving the connection open.

if you want to close the connection automatically when the reader is closed, you need to use an overload to the ExecuteReader method

 dr = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection);

http://msdn2.microsoft.com/en-us/library/y6wy5a0f.aspx|||What's the proper way to close the connection then?|||

Am I correct that this opens the connection:

SqlConnection myConn = new SqlConnection(connString);

And this closes it:

myConn.Dispose();

I guess my problem is that my GetDR function returns a DataReader leaving me no access to close myConn. Anything I can do about that?

|||

For some reason, I totally looked over your piece of code to close the connection when the reader is closed. It's exactly what I needed (obviously). Thanks for your help. Seems to be working great now!

mbanavige:

closing a reader does not automatically close the connection.

dr.close() simply closes the reader leaving the connection open.

if you want to close the connection automatically when the reader is closed, you need to use an overload to the ExecuteReader method

 dr = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection);

http://msdn2.microsoft.com/en-us/library/y6wy5a0f.aspx

Connection pooling in SQL 2K?

Is there any database connection pooling settings in SQL Server 2K?
Thank you,
AlwinSQL Server doesn't pool the connections. That comes from the front end or
middle tier. What ever it is that is making the connections.
--
Andrew J. Kelly SQL MVP
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:uWNHbaZtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Is there any database connection pooling settings in SQL Server 2K?
> Thank you,
> Alwin
>

Connection pooling in SQL 2K?

Is there any database connection pooling settings in SQL Server 2K?
Thank you,
Alwin
SQL Server doesn't pool the connections. That comes from the front end or
middle tier. What ever it is that is making the connections.
Andrew J. Kelly SQL MVP
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:uWNHbaZtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Is there any database connection pooling settings in SQL Server 2K?
> Thank you,
> Alwin
>
sqlsql

Connection pooling in SQL 2K?

Is there any database connection pooling settings in SQL Server 2K?
Thank you,
AlwinSQL Server doesn't pool the connections. That comes from the front end or
middle tier. What ever it is that is making the connections.
Andrew J. Kelly SQL MVP
"Alwin Mark" <AMark_SFO@.hotmail.com> wrote in message
news:uWNHbaZtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Is there any database connection pooling settings in SQL Server 2K?
> Thank you,
> Alwin
>

Connection pooling in MSSQL via VB

Hi folks,
Is there a way to implement connection pooling in MSSQL 7 or 2k when accessing it from VB?
Thanks.Yes, if you implement the VB data consuming object as a MTS COM object (COM DLL). MTS is already integrated in COM+ with w2k.

Originally posted by lokem
Hi folks,

Is there a way to implement connection pooling in MSSQL 7 or 2k when accessing it from VB?

Thanks.|||If you use ADO via ODBC, and you:

1. enable connection pooling in ODBC control panel
2. make sure you Close() your connection and set it to nothing when you've finished using it

...then you've got connection pooling. I think the closed connection objects hang around for a default of 60 seconds.|||Originally posted by dbadelphes
Yes, if you implement the VB data consuming object as a MTS COM object (COM DLL). MTS is already integrated in COM+ with w2k.

So I just have to get it registered to Component Services for a computer and I'm set? Do I need to do anything special when defining the connection string?

mwilkes: Thanks for the tip. Will be trying that out!|||To make Coonection Pooling work you also need to make sure that you use the same connection string all the time. In the past developers used the same UID, PWD, DATABASE and SERVER in the connection string but would change ApplicationName by setting it to a UserID so they could distinguish connections via IIS. This would end up creating a seperate connection.

Connection Pooling HELP !

I'm curious how connections are pooled under IIS. If you have several
applications setup under your website, does each one get its own pool? Or
is there only one pool created for all applications running under that
instance of IIS? What if two config files have connection strings which
specify a different number of connections in the pool...how is this
handled?
I'm curious as last night we noticed over 1500 connections to our SQL
Server...no wonder it is slammed. I'm wondering if we are getting multiple
pools of 100 connections and how to handle this...
I really appreciate any help on this....our live server is slammed and I'm
the one expected to come up with an answer ASAP.Every application gets its own pool. It would be stupid for one application
to wait for another to free up connections (think about running 2 websites
for 2 customers under the same IIS, neither would be happy to know his
application's performance - or lack of it :) - it being affected by the
running of the other).
Moreover, a pool is created for EVERY DISTINCT connection STRING, even if
the two connection strings are logically equivalent (you switch places of
fields, you add whitespace).
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uZz04fzVGHA.1736@.TK2MSFTNGP10.phx.gbl...
> I'm curious how connections are pooled under IIS. If you have several
> applications setup under your website, does each one get its own pool? Or
> is there only one pool created for all applications running under that
> instance of IIS? What if two config files have connection strings which
> specify a different number of connections in the pool...how is this
> handled?
> I'm curious as last night we noticed over 1500 connections to our SQL
> Server...no wonder it is slammed. I'm wondering if we are getting
> multiple pools of 100 connections and how to handle this...
> I really appreciate any help on this....our live server is slammed and
> I'm the one expected to come up with an answer ASAP.
>|||I understand about running two websites...but what about different
applications under the SAME website.
Hmmm I read on a MS website that the distinct connection string was not
true. That the only things that had to match was initial catalog,
connection timeout, user, and password. And it gave the project and various
connection string changes to show that the pool did not change. Here is the
link...
http://www.sql-server-performance.c...oling_myths.asp
It is hard to know what to believe. It is weird, even every MS developer
comes back with different answers.
Thank you so much for taking time to answer. I see a need to greatly reduce
our pool sizes.
"Gabriel Lacatus" <cyberdude@.nospam.nospam> wrote in message
news:uVJB8mzVGHA.4792@.TK2MSFTNGP14.phx.gbl...
> Every application gets its own pool. It would be stupid for one
> application to wait for another to free up connections (think about
> running 2 websites for 2 customers under the same IIS, neither would be
> happy to know his application's performance - or lack of it :) - it being
> affected by the running of the other).
> Moreover, a pool is created for EVERY DISTINCT connection STRING, even if
> the two connection strings are logically equivalent (you switch places of
> fields, you add whitespace).
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uZz04fzVGHA.1736@.TK2MSFTNGP10.phx.gbl...
>|||Pooling has changed over the years so there's a lot of out of date info
floating around. A pool is assigned per executable that has connections
open. Depending on your isolation setting in your IIS config, different
applications may or may not run in their own COM+ server process. Look at
the connections in sysprocesses and see if they are all for the same user or
for different users. Also make sure connections are being closed so they
are returned to the pool. If you have 1500 open connections and you don't
have 15 svchost processes running, either it's likely you are opening
connections past the pool size because MDAC doesn't think the connections
are reusable or you are not closing connections.
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
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:OzPouszVGHA.5364@.tk2msftngp13.phx.gbl...
>I understand about running two websites...but what about different
>applications under the SAME website.
> Hmmm I read on a MS website that the distinct connection string was not
> true. That the only things that had to match was initial catalog,
> connection timeout, user, and password. And it gave the project and
> various connection string changes to show that the pool did not change.
> Here is the link...
> http://www.sql-server-performance.c...oling_myths.asp
> It is hard to know what to believe. It is weird, even every MS developer
> comes back with different answers.
> Thank you so much for taking time to answer. I see a need to greatly
> reduce our pool sizes.
>
> "Gabriel Lacatus" <cyberdude@.nospam.nospam> wrote in message
> news:uVJB8mzVGHA.4792@.TK2MSFTNGP14.phx.gbl...
>

Connection Pooling for Data-Driven Subscriptions?

Is there any way to get a data-driven subscription to share the DB
Connection between iterrations?
The behaviour we're seeing currently is a new connection being opened/closed
for each report delivered by our data-driven subscription. This is impacting
our server perf.
Suggestions? Pointers?
Did I miss anything in the docs?
TIAAre they all using the same credentials?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Paul Enfield" <penfield@.paraport.com> wrote in message
news:uFipWYyVFHA.3588@.TK2MSFTNGP10.phx.gbl...
> Is there any way to get a data-driven subscription to share the DB
> Connection between iterrations?
> The behaviour we're seeing currently is a new connection being
opened/closed
> for each report delivered by our data-driven subscription. This is
impacting
> our server perf.
> Suggestions? Pointers?
> Did I miss anything in the docs?
> TIA
>

connection pooling for access jet connected ODBC tables?

Is 'connection pooling' available when using an access front end connecting
to sql server 2000 tables via jet connected ODBC (not an access
project/ado)?
If yes, is there any configurable options? if yes where? On client side or
server side or both?
any help is appreciated. Thanks.
You can configure ODBC connection properties in an Access mdb in
several places -- database options, query options, and in an ODBC
connection string (or DSN). However, I'm not sure about
resource/connection pooling. Access handles a lot of that under the
covers, spawning additonal connections when needed, say for opening a
complex form. What exactly are you trying to dos?
--Mary
On Mon, 24 May 2004 16:37:11 -0400, "djc" <noone@.nowhere.com> wrote:

>Is 'connection pooling' available when using an access front end connecting
>to sql server 2000 tables via jet connected ODBC (not an access
>project/ado)?
>If yes, is there any configurable options? if yes where? On client side or
>server side or both?
>any help is appreciated. Thanks.
>
sqlsql

connection pooling for access jet connected ODBC tables?

Is 'connection pooling' available when using an access front end connecting
to sql server 2000 tables via jet connected ODBC (not an access
project/ado)?
If yes, is there any configurable options? if yes where? On client side or
server side or both?
any help is appreciated. Thanks.You can configure ODBC connection properties in an Access mdb in
several places -- database options, query options, and in an ODBC
connection string (or DSN). However, I'm not sure about
resource/connection pooling. Access handles a lot of that under the
covers, spawning additonal connections when needed, say for opening a
complex form. What exactly are you trying to dos?
--Mary
On Mon, 24 May 2004 16:37:11 -0400, "djc" <noone@.nowhere.com> wrote:

>Is 'connection pooling' available when using an access front end connecting
>to sql server 2000 tables via jet connected ODBC (not an access
>project/ado)?
>If yes, is there any configurable options? if yes where? On client side or
>server side or both?
>any help is appreciated. Thanks.
>

connection pooling feature with ADO?[fixed question]

I know that ADO with SQL Server has connection pooling feature. Considering
this, I cannot understand why the problem described below occurs.
Please reply me any solution. Thanks in advance.
Regards,
Hyun-jik Bae
Environment:
SQL Server 2000
ADO 2.8
Condition:
Simultaneous one thread opens ADO connection, updates a record and closes
it. This is done 50 times per second.
Problem:
After running the condition I mentioned above for about a minute, a lot of
TCP connection to 1433 on TIME_WAIT state remains.Hi
How many connections are shown by sp_who2 during and after this period?
Regards
Mike
"Bae,Hyun-jik" wrote:

> I know that ADO with SQL Server has connection pooling feature. Considerin
g
> this, I cannot understand why the problem described below occurs.
> Please reply me any solution. Thanks in advance.
> Regards,
> Hyun-jik Bae
> --
> Environment:
> SQL Server 2000
> ADO 2.8
> Condition:
> Simultaneous one thread opens ADO connection, updates a record and closes
> it. This is done 50 times per second.
> Problem:
> After running the condition I mentioned above for about a minute, a lot of
> TCP connection to 1433 on TIME_WAIT state remains.
>
>
>|||sp_who2 says there is only one during that period. However, a lot of 1433
TCP connections still exists.
Regards,
Hyun-jik
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:D8ABCB63-D676-46B4-A152-90F084FD58BB@.microsoft.com...
> Hi
> How many connections are shown by sp_who2 during and after this period?
> Regards
> Mike
> "Bae,Hyun-jik" wrote:
>

connection pooling feature with ADO?

I know that ADO with SQL Server has connection pooling feature. Considering
this, I cannot understand why the problem described below occurs.
Please reply me any solution. Thanks in advance.
Regards,
Hyun-jik Bae
Environment:
SQL Server 2000
ADO 2.8
Condition:
Simultaneous one thread opens ADO connection, updates a record and closes
it. This is done 50 times per second.
Problem:
After running the condition I mentioned above for about a minute, every ADO
Connection object in a program occupies a TCP connection to 1433.Hi
If you are using ODBC then it might not be enabled see
http://www.aspfaq.com/show.asp?id=2155
You may also want to look at
http://msdn.microsoft.com/library/d...
ng2.asp
John
"Bae,Hyun-jik" wrote:

> I know that ADO with SQL Server has connection pooling feature. Considerin
g
> this, I cannot understand why the problem described below occurs.
> Please reply me any solution. Thanks in advance.
> Regards,
> Hyun-jik Bae
> --
> Environment:
> SQL Server 2000
> ADO 2.8
> Condition:
> Simultaneous one thread opens ADO connection, updates a record and closes
> it. This is done 50 times per second.
> Problem:
> After running the condition I mentioned above for about a minute, every AD
O
> Connection object in a program occupies a TCP connection to 1433.
>
>