Timeout Expired on first System.Data.Common.DbDataAdapter.Fill

  • Thread starter Jonathan Levine
  • Start date
J

Jonathan Levine

Hi,

I frequently see a "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" message on the first System.Data.Common.DbDataAdapter.Fill()
executed for each Sql Connection string (I have 4 of them, each used
for connecting to various databases).

I see this most consistantly on my development machine. On the dev
machine, this happens fairly consistantly on the first
System.Data.Common.DbDataAdapter.Fill() for each connection string
after a recompile. But I also occasionally see it on my production
box after a deployment.

Most of the responses to similar problems tend to be "don't forget
to call SqlConnection.Close();" however, I think this isn't a problem
here, as the exception only occurs on the FIRST .Fill() for each
connection. If I hit refresh, or if I go to another page that uses
the same connection string, I don't see the exception.

Sample connection string:

workstation id=Ranking_WebApp;packet size=8192;pooling=true;min pool
size=2;data source=XXXX;persist security info=False;initial
catalog=Ranking;user id=yyy;password=zzz;connection timeout=0;min pool
size=1

Sample exception traceback:
[InvalidOperationException: 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.]
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +396
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection
connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Ranking.Component1.FillDataSet(HelpJobDataSet dset) in
c:\inetpub\wwwroot\Ranking\Component1.vb:587
Ranking._Default.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\Ranking\Default.aspx.vb:48
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +720

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573; MSDE Version 7.1.3088; System running
Windows 2000 SP4 + ADO 2.80.1022.0

Thanks for your help...

-- J
 
M

Miha Markic [MVP C#]

Hi Jonathan,

At first glance I see that you have twice the min pool size definition with
different values.
Also, try to omit connection timeout or set it to a higher number, for
example to 15.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Jonathan Levine said:
Hi,

I frequently see a "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" message on the first System.Data.Common.DbDataAdapter.Fill()
executed for each Sql Connection string (I have 4 of them, each used
for connecting to various databases).

I see this most consistantly on my development machine. On the dev
machine, this happens fairly consistantly on the first
System.Data.Common.DbDataAdapter.Fill() for each connection string
after a recompile. But I also occasionally see it on my production
box after a deployment.

Most of the responses to similar problems tend to be "don't forget
to call SqlConnection.Close();" however, I think this isn't a problem
here, as the exception only occurs on the FIRST .Fill() for each
connection. If I hit refresh, or if I go to another page that uses
the same connection string, I don't see the exception.

Sample connection string:

workstation id=Ranking_WebApp;packet size=8192;pooling=true;min pool
size=2;data source=XXXX;persist security info=False;initial
catalog=Ranking;user id=yyy;password=zzz;connection timeout=0;min pool
size=1

Sample exception traceback:
[InvalidOperationException: 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.]
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString
options, Boolean& isInTransaction) +396
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection
connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Ranking.Component1.FillDataSet(HelpJobDataSet dset) in
c:\inetpub\wwwroot\Ranking\Component1.vb:587
Ranking._Default.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\Ranking\Default.aspx.vb:48
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +720

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573; MSDE Version 7.1.3088; System running
Windows 2000 SP4 + ADO 2.80.1022.0

Thanks for your help...

-- J
Click to expand...
 
P

Patrice

You could also try without the min pool size (also you used it two times) to
see what happens...

Patrice


Jonathan Levine said:
Hi,

I frequently see a "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" message on the first System.Data.Common.DbDataAdapter.Fill()
executed for each Sql Connection string (I have 4 of them, each used
for connecting to various databases).

I see this most consistantly on my development machine. On the dev
machine, this happens fairly consistantly on the first
System.Data.Common.DbDataAdapter.Fill() for each connection string
after a recompile. But I also occasionally see it on my production
box after a deployment.

Most of the responses to similar problems tend to be "don't forget
to call SqlConnection.Close();" however, I think this isn't a problem
here, as the exception only occurs on the FIRST .Fill() for each
connection. If I hit refresh, or if I go to another page that uses
the same connection string, I don't see the exception.

Sample connection string:

workstation id=Ranking_WebApp;packet size=8192;pooling=true;min pool
size=2;data source=XXXX;persist security info=False;initial
catalog=Ranking;user id=yyy;password=zzz;connection timeout=0;min pool
size=1

Sample exception traceback:
[InvalidOperationException: 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.]
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString
options, Boolean& isInTransaction) +396
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection
connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Ranking.Component1.FillDataSet(HelpJobDataSet dset) in
c:\inetpub\wwwroot\Ranking\Component1.vb:587
Ranking._Default.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\Ranking\Default.aspx.vb:48
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +720

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573; MSDE Version 7.1.3088; System running
Windows 2000 SP4 + ADO 2.80.1022.0

Thanks for your help...

-- J
Click to expand...
 
J

Jonathan Levine

Thanks Patrice and Miha.

It turned out to be min pool size. Even when I only have one min pool
size specified (doh!) as min pool size=1, I get the timeout. The
connection timeout seems to work as documented, Miha (connection
timeout=0 means wait indefinitely, cf.
http://msdn.microsoft.com/library/d...ionClassConnectionTimeoutTopic.asp?frame=true).

Thanks again for quick and accurate suggestions!

-- J

Patrice said:
You could also try without the min pool size (also you used it two times) to
see what happens...

Patrice


Jonathan Levine said:
Hi,

I frequently see a "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" message on the first System.Data.Common.DbDataAdapter.Fill()
executed for each Sql Connection string (I have 4 of them, each used
for connecting to various databases).

I see this most consistantly on my development machine. On the dev
machine, this happens fairly consistantly on the first
System.Data.Common.DbDataAdapter.Fill() for each connection string
after a recompile. But I also occasionally see it on my production
box after a deployment.

Most of the responses to similar problems tend to be "don't forget
to call SqlConnection.Close();" however, I think this isn't a problem
here, as the exception only occurs on the FIRST .Fill() for each
connection. If I hit refresh, or if I go to another page that uses
the same connection string, I don't see the exception.

Sample connection string:

workstation id=Ranking_WebApp;packet size=8192;pooling=true;min pool
size=2;data source=XXXX;persist security info=False;initial
catalog=Ranking;user id=yyy;password=zzz;connection timeout=0;min pool
size=1

Sample exception traceback:
[InvalidOperationException: 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.]
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString
options, Boolean& isInTransaction) +396
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection
connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Ranking.Component1.FillDataSet(HelpJobDataSet dset) in
c:\inetpub\wwwroot\Ranking\Component1.vb:587
Ranking._Default.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\Ranking\Default.aspx.vb:48
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +720

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573; MSDE Version 7.1.3088; System running
Windows 2000 SP4 + ADO 2.80.1022.0

Thanks for your help...

-- J
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

System.OutOfMemoryException in DataAdapter 4
On TransactionScope and TableAdapter 0
Error Message: Timeout expired. 2
DataAdapter Fill Exception 3
Timeout Error 1
System.AccessViolationException 1
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign 4
ADO.NET TableAdapter "The connection is closed" error 0

Top