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 and timeouts

Does anyone have suggestions, other than using "set xact_abort on," on how
to handle rolling back a transaction after it's timed out while using
connection pooling?Richard wrote:
> Does anyone have suggestions, other than using "set xact_abort on,"
> on how to handle rolling back a transaction after it's timed out
> while using connection pooling?
Why can't the business layer executing the SQL issue a rollback if a
timeout error is returned from the call?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||That's the only way to handle it I guess. Thanks!
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23c0ci9fkFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> Why can't the business layer executing the SQL issue a rollback if a
> timeout error is returned from the call?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

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.

connection level NOLOCK

Hi!

when WITH (NOLOCK) statement is used in a select statement, it allows to retrieved data from a set of data locked by another transaction. Is it possible to enforce this at connection level?

I mean to open a connection using sqlclient and enforce NOLOCK so that all select statements post to database using that connection are not locked by other transactions.

thanks in advance

Ok. I found it!

I have to begin a transaction with isolation level 'readuncommited' and do all select statements inside that transaction.

Connection / Transaction Statistics

I'm trying to capture the number of connections that are made to each
database on our server (whether by middleware clients, QA, etc).
Preferrably, I'd like to be able to capture this information on a daily
basis. Is this possible using SQL Server 2000? I've researched this to no
end and found information on the sysprocesses table, but this returns current
connection information with no historical data. Thanks for any ideas.
You could look at using profiler to cature this information
See Monitoring with SQL Profiler under Administering SQL Server in BOL
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> I'm trying to capture the number of connections that are made to each
> database on our server (whether by middleware clients, QA, etc).
> Preferrably, I'd like to be able to capture this information on a daily
> basis. Is this possible using SQL Server 2000? I've researched this to
no
> end and found information on the sysprocesses table, but this returns
current
> connection information with no historical data. Thanks for any ideas.
|||I've done this and am unable to get the server instance or the database name
to log. The database is more important. The trace shows the login name and
application for table scan events. I have the database name selected but it
isn't populating. Am I missing something simple?
"Simon Worth" wrote:

> You could look at using profiler to cature this information
> See Monitoring with SQL Profiler under Administering SQL Server in BOL
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> no
> current
>
>
|||You're right, the database name won't come through, but ussually database id
does. You can find out what the name of the database is by using the id on
sysdatabases in master like this
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
WHERE dbid = <<The id from profiler>>
ORDER BY dbid
GO
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> I've done this and am unable to get the server instance or the database
name
> to log. The database is more important. The trace shows the login name
and
> application for table scan events. I have the database name selected but
it[vbcol=seagreen]
> isn't populating. Am I missing something simple?
> "Simon Worth" wrote:
daily[vbcol=seagreen]
to[vbcol=seagreen]
|||Easier still is just use DB_NAME, select DB_NAME (yournumber)
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:%23s$pCCOIFHA.1996@.TK2MSFTNGP12.phx.gbl...
> You're right, the database name won't come through, but ussually database
id
> does. You can find out what the name of the database is by using the id
on[vbcol=seagreen]
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> name
> and
but[vbcol=seagreen]
> it
message[vbcol=seagreen]
each[vbcol=seagreen]
> daily
this[vbcol=seagreen]
> to
returns[vbcol=seagreen]
ideas.
>
|||Very cools tips, thank you. My last question is how I can have this run
without having to have a Trace Window open. Is there another tool/process
that can compile this same information and write it to a table?
"Simon Worth" wrote:

> You're right, the database name won't come through, but ussually database id
> does. You can find out what the name of the database is by using the id on
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> name
> and
> it
> daily
> to
>
>

Connection / Transaction Statistics

I'm trying to capture the number of connections that are made to each
database on our server (whether by middleware clients, QA, etc).
Preferrably, I'd like to be able to capture this information on a daily
basis. Is this possible using SQL Server 2000? I've researched this to no
end and found information on the sysprocesses table, but this returns current
connection information with no historical data. Thanks for any ideas.You could look at using profiler to cature this information
See Monitoring with SQL Profiler under Administering SQL Server in BOL
--
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> I'm trying to capture the number of connections that are made to each
> database on our server (whether by middleware clients, QA, etc).
> Preferrably, I'd like to be able to capture this information on a daily
> basis. Is this possible using SQL Server 2000? I've researched this to
no
> end and found information on the sysprocesses table, but this returns
current
> connection information with no historical data. Thanks for any ideas.|||I've done this and am unable to get the server instance or the database name
to log. The database is more important. The trace shows the login name and
application for table scan events. I have the database name selected but it
isn't populating. Am I missing something simple?
"Simon Worth" wrote:
> You could look at using profiler to cature this information
> See Monitoring with SQL Profiler under Administering SQL Server in BOL
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> > I'm trying to capture the number of connections that are made to each
> > database on our server (whether by middleware clients, QA, etc).
> > Preferrably, I'd like to be able to capture this information on a daily
> > basis. Is this possible using SQL Server 2000? I've researched this to
> no
> > end and found information on the sysprocesses table, but this returns
> current
> > connection information with no historical data. Thanks for any ideas.
>
>|||You're right, the database name won't come through, but ussually database id
does. You can find out what the name of the database is by using the id on
sysdatabases in master like this
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
WHERE dbid = <<The id from profiler>>
ORDER BY dbid
GO
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> I've done this and am unable to get the server instance or the database
name
> to log. The database is more important. The trace shows the login name
and
> application for table scan events. I have the database name selected but
it
> isn't populating. Am I missing something simple?
> "Simon Worth" wrote:
> > You could look at using profiler to cature this information
> > See Monitoring with SQL Profiler under Administering SQL Server in BOL
> >
> > --
> > Simon Worth
> >
> >
> > "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> > news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> > > I'm trying to capture the number of connections that are made to each
> > > database on our server (whether by middleware clients, QA, etc).
> > > Preferrably, I'd like to be able to capture this information on a
daily
> > > basis. Is this possible using SQL Server 2000? I've researched this
to
> > no
> > > end and found information on the sysprocesses table, but this returns
> > current
> > > connection information with no historical data. Thanks for any ideas.
> >
> >
> >|||Easier still is just use DB_NAME, select DB_NAME (yournumber)
--
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:%23s$pCCOIFHA.1996@.TK2MSFTNGP12.phx.gbl...
> You're right, the database name won't come through, but ussually database
id
> does. You can find out what the name of the database is by using the id
on
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> > I've done this and am unable to get the server instance or the database
> name
> > to log. The database is more important. The trace shows the login name
> and
> > application for table scan events. I have the database name selected
but
> it
> > isn't populating. Am I missing something simple?
> >
> > "Simon Worth" wrote:
> >
> > > You could look at using profiler to cature this information
> > > See Monitoring with SQL Profiler under Administering SQL Server in BOL
> > >
> > > --
> > > Simon Worth
> > >
> > >
> > > "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in
message
> > > news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> > > > I'm trying to capture the number of connections that are made to
each
> > > > database on our server (whether by middleware clients, QA, etc).
> > > > Preferrably, I'd like to be able to capture this information on a
> daily
> > > > basis. Is this possible using SQL Server 2000? I've researched
this
> to
> > > no
> > > > end and found information on the sysprocesses table, but this
returns
> > > current
> > > > connection information with no historical data. Thanks for any
ideas.
> > >
> > >
> > >
>|||Very cools tips, thank you. My last question is how I can have this run
without having to have a Trace Window open. Is there another tool/process
that can compile this same information and write it to a table?
"Simon Worth" wrote:
> You're right, the database name won't come through, but ussually database id
> does. You can find out what the name of the database is by using the id on
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> > I've done this and am unable to get the server instance or the database
> name
> > to log. The database is more important. The trace shows the login name
> and
> > application for table scan events. I have the database name selected but
> it
> > isn't populating. Am I missing something simple?
> >
> > "Simon Worth" wrote:
> >
> > > You could look at using profiler to cature this information
> > > See Monitoring with SQL Profiler under Administering SQL Server in BOL
> > >
> > > --
> > > Simon Worth
> > >
> > >
> > > "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> > > news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> > > > I'm trying to capture the number of connections that are made to each
> > > > database on our server (whether by middleware clients, QA, etc).
> > > > Preferrably, I'd like to be able to capture this information on a
> daily
> > > > basis. Is this possible using SQL Server 2000? I've researched this
> to
> > > no
> > > > end and found information on the sysprocesses table, but this returns
> > > current
> > > > connection information with no historical data. Thanks for any ideas.
> > >
> > >
> > >
>
>

Connection / Transaction Statistics

I'm trying to capture the number of connections that are made to each
database on our server (whether by middleware clients, QA, etc).
Preferrably, I'd like to be able to capture this information on a daily
basis. Is this possible using SQL Server 2000? I've researched this to no
end and found information on the sysprocesses table, but this returns curren
t
connection information with no historical data. Thanks for any ideas.You could look at using profiler to cature this information
See Monitoring with SQL Profiler under Administering SQL Server in BOL
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> I'm trying to capture the number of connections that are made to each
> database on our server (whether by middleware clients, QA, etc).
> Preferrably, I'd like to be able to capture this information on a daily
> basis. Is this possible using SQL Server 2000? I've researched this to
no
> end and found information on the sysprocesses table, but this returns
current
> connection information with no historical data. Thanks for any ideas.|||I've done this and am unable to get the server instance or the database name
to log. The database is more important. The trace shows the login name and
application for table scan events. I have the database name selected but it
isn't populating. Am I missing something simple?
"Simon Worth" wrote:

> You could look at using profiler to cature this information
> See Monitoring with SQL Profiler under Administering SQL Server in BOL
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:9ED15FFB-0956-4557-8824-DF9870B92A99@.microsoft.com...
> no
> current
>
>|||You're right, the database name won't come through, but ussually database id
does. You can find out what the name of the database is by using the id on
sysdatabases in master like this
USE master
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM sysdatabases
WHERE dbid = <<The id from profiler>>
ORDER BY dbid
GO
Simon Worth
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> I've done this and am unable to get the server instance or the database
name
> to log. The database is more important. The trace shows the login name
and
> application for table scan events. I have the database name selected but
it[vbcol=seagreen]
> isn't populating. Am I missing something simple?
> "Simon Worth" wrote:
>
daily[vbcol=seagreen]
to[vbcol=seagreen]|||Easier still is just use DB_NAME, select DB_NAME (yournumber)
Simon Worth
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message
news:%23s$pCCOIFHA.1996@.TK2MSFTNGP12.phx.gbl...
> You're right, the database name won't come through, but ussually database
id
> does. You can find out what the name of the database is by using the id
on
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> name
> and
but[vbcol=seagreen]
> it
message[vbcol=seagreen]
each[vbcol=seagreen]
> daily
this[vbcol=seagreen]
> to
returns[vbcol=seagreen]
ideas.[vbcol=seagreen]
>|||Very cools tips, thank you. My last question is how I can have this run
without having to have a Trace Window open. Is there another tool/process
that can compile this same information and write it to a table?
"Simon Worth" wrote:

> You're right, the database name won't come through, but ussually database
id
> does. You can find out what the name of the database is by using the id o
n
> sysdatabases in master like this
> USE master
> SELECT dbid, DB_NAME(dbid) AS DB_NAME
> FROM sysdatabases
> WHERE dbid = <<The id from profiler>>
> ORDER BY dbid
> GO
>
> --
> Simon Worth
>
> "Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
> news:34BB2F77-E630-4042-A448-3503E576CB70@.microsoft.com...
> name
> and
> it
> daily
> to
>
>