Timeout expired for connection pooling

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Timeout expired for connection pooling

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-31 : 17:20:49

I'm getting the following error:

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.

Not sure why, since I'm trying to close the connection right when I'm done. I have the timeout set to "30"

Select Case DBselected
Case "DB8"
Try
Dim sCon1 As New SqlConnection
sCon1.ConnectionString = ConfigurationSettings.AppSettings("db8")
Session("DBDDL") = sCon1.ConnectionString.ToString()
sCon1.Open()

Dim cmd As New SqlCommand("[usp_Security]", sCon1)
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50))
cmd.Parameters("@IDNumber").Value = Session("User")

Dim DR As SqlDataReader = cmd.ExecuteReader
If DR.HasRows = False Then

btnSwitchboard1.Enabled = False
lblnotice.Visible = True
lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!"
Else
btnSwitchboard1.Enabled = True
lblnotice.Visible = False
End If
DR.Close()
DR = Nothing
Catch ex As Exception
lblStatus.Text = "Error building IDNumber: " & ex.Message
Finally
sCon1.Close()
End Try

Case "DB1"
Try

Dim sCon1 As New SqlConnection
sCon1.ConnectionString = ConfigurationSettings.AppSettings("db1")
Session("DBDDL") = sCon1.ConnectionString.ToString()
sCon1.Open()

Dim cmd As New SqlCommand("[usp_Security]", sCon1)
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50))
cmd.Parameters("@IDNumber").Value = Session("User")

Dim DR As SqlDataReader = cmd.ExecuteReader
If DR.HasRows = False Then
btnSwitchboard1.Enabled = False
lblnotice.Visible = True
lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!"
Else

btnSwitchboard1.Enabled = True
lblnotice.Visible = False
End If
DR.Close()
DR = Nothing
Catch ex As Exception
lblStatus.Text = "Error building IDNumber: " & ex.Message
Finally
sCon1.Close()
End Try

Case "DB2"
Try

Dim sCon1 As New SqlConnection
sCon1.ConnectionString = ConfigurationSettings.AppSettings("db2")
Session("DBDDL") = sCon1.ConnectionString.ToString()
sCon1.Open()

Dim cmd As New SqlCommand("[usp_Security]", sCon1)
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50))
cmd.Parameters("@IDNumber").Value = Session("User")
Dim DR As SqlDataReader = cmd.ExecuteReader
If DR.HasRows = False Then

btnSwitchboard1.Enabled = False
lblnotice.Visible = True
lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!"
Else

btnSwitchboard1.Enabled = True
lblnotice.Visible = False
End If
DR.Close()
DR = Nothing
Catch ex As Exception
lblStatus.Text = "Error building IDNumber: " & ex.Message
Finally
sCon1.Close()
End Try

Case "DB3"
Try

Dim sCon1 As New SqlConnection
sCon1.ConnectionString = ConfigurationSettings.AppSettings("db3")
Session("DBDDL") = sCon1.ConnectionString.ToString()
sCon1.Open()

Dim cmd As New SqlCommand("[usp_Security]", sCon1)
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50))
cmd.Parameters("@IDNumber").Value = Session("User")
Dim DR As SqlDataReader = cmd.ExecuteReader
If DR.HasRows = False Then
btnSwitchboard1.Enabled = False
lblnotice.Visible = True
lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!"
Else

btnSwitchboard1.Enabled = True
lblnotice.Visible = False
End If
DR.Close()
DR = Nothing
Catch ex As Exception
lblStatus.Text = "Error building IDNumber: " & ex.Message
Finally
sCon1.Close()
End Try

Case "DB4"
Try

Dim sCon1 As New SqlConnection
sCon1.ConnectionString = ConfigurationSettings.AppSettings("db4")
Session("DBDDL") = sCon1.ConnectionString.ToString()
sCon1.Open()

Dim cmd As New SqlCommand("[usp_Security]", sCon1)
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50))
cmd.Parameters("@IDNumber").Value = Session("User")
Dim DR As SqlDataReader = cmd.ExecuteReader
If DR.HasRows = False Then

btnSwitchboard1.Enabled = False
lblnotice.Visible = True
lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!"
Else

btnSwitchboard1.Enabled = True
lblnotice.Visible = False
End If
DR.Close()
DR = Nothing
Catch ex As Exception
lblStatus.Text = "Error building IDNumber: " & ex.Message
Finally
sCon1.Close()
End Try

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 17:42:17

How long does the query take to run when using the same parameters in Query Analyzer?

Tara Kizer
aka tduggan

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-31 : 18:00:58

I'm not sure, but the table has that field as the PK, and it's roughly less than 400 rows for each database.

I'll try to run that tomorrow when I get in to work and see.

Thanks for the suggestion!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-31 : 18:03:51

You can also use SQL Profiler to check the duration of the query. Just make sure you start it prior to generating the error in your application.

Tara Kizer
aka tduggan

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-05-31 : 18:20:56

Are you running against a MSDE database server, or SQL server?
When you get this error, run sp_who2 in QA to see how many actual connections you have to SQL server.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-31 : 20:06:06

I'm using SQL Server.

Thanks, I'll try that tomorrow too!

I know that these connections, in web.config, are used for all the other pages as well. For my Datareaders, I close them and "= Nothing" too.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2006-05-31 : 20:16:46

You might not be closing your datareaders if you are catching errors.

Also that is a lot of code, try something like this. If you do catch an error, the cmd.ExecuteReader(CommandBehavior.CloseConnection) should close the reader as well as the database connection. In your code you are closing the connection but not necessarily closing your datareaders.

Another thing, you are storing your connection string in a session variable. Session("DBDDL") = sCon1.ConnectionString.ToString() I don't see any need for that.

Also, you are asking for a datareader but you don't seem to be reading any data? You could return a integer indicating the status as opposed to the overhead of a sqldatareader.


        Select Case DBselected
            Case "DB8"

                '## Create Instance of Connection and Command Object
                Dim sCon1 As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("db8"))
                Dim cmd As SqlCommand = New SqlCommand("[usp_Security]", sCon1)
                Dim DR As SqlDataReader

                '## Mark the Command as a SPROC
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IDNumber", System.Data.SqlDbType.NVarChar, 50))
                cmd.Parameters("@IDNumber").Value = Session("User")

                Try
                    sCon1.Open()
                    DR = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                    If Not DR.HasRows Then
                        btnSwitchboard1.Enabled = False
                        lblnotice.Visible = True
                        lblnotice.Text = "The IDNumber, " & strIDNumber & ", doesn't exist in the database for access permission!"
                    Else
                        btnSwitchboard1.Enabled = True
                        lblnotice.Visible = False
                    End If
                    DR.Close()
                Catch ex As Exception
                    lblStatus.Text = "Error building IDNumber: " & ex.Message
                Finally
                    sCon1.Close()
                End Try

        End Select

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-31 : 22:33:20

Jbelthoff,

Thanks for the reply!

We set the session("DBDDL") in order to be used on the subsequent pages for the user.

We have 14 different databases, so whatever the user selects from a db dropdownlist, we use that as a session variable.

Yeah...you're right...maybe I should do an ExecuteScalar and do a count to see if the user exists in the table or not.

Thanks!

JBelthoff
Posting Yak Master

173 Posts

Posted - 2006-06-01 : 07:43:01

Actually instead of Execute Scalar, I would go with Cmd.ExecuteNonQuery

Than just use an output parameter as an indicator of the users status. But that's just me.

However, your original problem is saying that either your max connection in your pool is not high enough, or somehwere you are not closing your connections. Also in order to use the connection pool you must use the same connection strings exactly.

Other than your multiple DB's from what I see you seem to be doing that.

You could increase your Max Pool by looking here: http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/connectionpooling.aspx

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources