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

  • Thread starter Thread starter Jonathan Levine
  • Start date 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
 
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...
 
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...
 
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...
 
Back
Top