Connection Pooling issue - SQL Server Forums

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

Reporting Services Development

Connection Pooling issue

Author

Topic

mamin
Starting Member

5 Posts

Posted - 2005-10-26 : 14:29:48

I have a progam where I am issuing a UPDATE statement in loop and sometime it just hangs. Also, it takes up all the max. no. of allowed (300 connections) connections for SQL Server. I increated this no. from 100 to 300 to resolve this issue but still I am having this issue once in a while (not the all the time). I do not want to increase max. limit since it could affect the server overall perfomance.

Seems like above is happening due to connection leak somewhere in .NET program I am running. I checked every single line of code and I am positive that connection is being closed using close() as well as Dispose() method after use. I am investigating this issue to make sure some other program is not creating this issue. In meantime I like to know how can I clear any unused connection manually (May be using Stored Procedure etc) so after knowing the issue can be corrected temporarily.

Also, please provide me any pointers in .Net where I should look to check connection leak. I am using VB.net 1.1 version

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-26 : 14:41:22

Not sure why this is in the Reporting Services forum...

You can issue a KILL statement inside Querly Analyzer to kill the spids.

Why do you need to run a loop to UPDATE rows? Can you post the code?

Tara

mamin
Starting Member

5 Posts

Posted - 2005-10-26 : 19:07:02

Sorry I am new to this forum so I was not sure but it is related to .Net and SQL Server 2000 related.

After this post I realized that issue was not max. connection but it is database row lock issue.

Error message I am getting is 'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding'

Here is the code

===============================================
Public Function finalizing()
'Dim tot_amt = 0.0
Dim MySQLs As String
Dim i As Integer = 0

rows = objDateSets.Tables("MyCart").Rows.Count

i = 0
Dim con As New System.Data.SqlClient.SqlConnection()
Dim connstring As String
connstring = ConfigurationSettings.AppSettings("ConnectionString")
con.ConnectionString = connstring
con.Open()
Dim sql_cmd As SqlCommand
Dim uQuantity, strQuantityUpdate, strQuantitySold, uProductId, empty_cart

Dim Store_Qty, WareHouse_Qty, tblproduct_qty, tblproduct_qty1, n_QTag
Store_Qty = 0
WareHouse_Qty = 0

While (i < rows)
Store_Qty = 0
WareHouse_Qty = 0
uProductId = Trim(objDateSets.Tables("MyCart").Rows(i)(0))
uQuantity = Trim(objDateSets.Tables("MyCart").Rows(i)(6))
uQuantity = CInt(uQuantity)
tblproduct_qty = objDateSets.Tables("MyCart").Rows(i)(13)
tblproduct_qty1 = objDateSets.Tables("MyCart").Rows(i)(15)
n_QTag = Trim(objDateSets.Tables("MyCart").Rows(i)(11))
If (IsNumeric(tblproduct_qty) = False Or tblproduct_qty < 0) Then
tblproduct_qty = 0
End If
If (IsNumeric(tblproduct_qty1) = False Or tblproduct_qty1 < 0) Then
tblproduct_qty1 = 0
End If
If (UCase(n_QTag) = "YES") Then
If (Store_Qty > 0 And WareHouse_Qty > 0) Then
strQuantityUpdate = "UPDATE tblProduct SET Quantity = Quantity - " & Store_Qty & ",Quantity1 = Quantity1 - " & WareHouse_Qty
strQuantityUpdate = strQuantityUpdate & " WHERE ProductId= '" & uProductId & "' and Q_tag = 'YES';"
ElseIf (Store_Qty > 0 And WareHouse_Qty = 0) Then
strQuantityUpdate = "UPDATE tblProduct SET Quantity = Quantity - " & Store_Qty
strQuantityUpdate = strQuantityUpdate & " WHERE ProductId= '" & uProductId & "' and Q_tag = 'YES';"
ElseIf (Store_Qty = 0 And WareHouse_Qty > 0) Then
strQuantityUpdate = "UPDATE tblProduct SET Quantity1 = Quantity1 - " & WareHouse_Qty
strQuantityUpdate = strQuantityUpdate & " WHERE ProductId= '" & uProductId & "' and Q_tag = 'YES';"
End If

Try
sql_cmd = New SqlCommand(strQuantityUpdate, con)
sql_cmd.ExecuteNonQuery()
Catch Ex As Exception
send_errormail(Ex, strQuantityUpdate & "<br>" & ccErrorMessage & "<br>While updating Quantity of Product In Process.ASPX")
End Try

End If
i = i + 1
End While

con.Close()
con.Dispose()
con = Nothing
End Function
=============================================================================================

I am having issue with UDPATE Statement. Not with all the data..but not exactly sure what type of data is creating this issue. I am assuming when same row is being updated in loop next time when it tries to update same row ...it finds record locks and it hangs..

I like to know if I can manually clear all the LOCKS on given table by issuing SQL Statement for work around to make sure rest of update can be process.

and If possible how can I make sure LOCK is being released correctly in VB.Net code so it never happens again

Thanks In Advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-26 : 19:20:07

I can't help you with this code as it isn't pure T-SQL. What I'd suggest doing is rewriting this as a stored procedure and checking how long it takes to run inside SQL Server and not from within your application.

The default timeout value is typically 30 seconds. Your query is taking longer than 30 seconds to execute, hence the error. You either need to optimize the query, change the database design, switch to stored procedures, use a set-based method instead of looping, or increase your timeout value in the conection string.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources