Can't seem to keep sqlconnections closed.

I used .close(), .dispose() and modified my connection string...

Page 1 of 1

6 Replies - 1711 Views - Last Post: 03 December 2008 - 01:01 PM Rate Topic: -----

#1 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Can't seem to keep sqlconnections closed.

Posted 01 December 2008 - 05:36 PM

Problem:
People are getting errors that there are no pooled connections available.

To check pooled connections I do this in SQL:
select program_name, count(*)
from master..sysprocesses
where program_name like '%.Net%'
group by program_name
order by count(*) desc


If I restart IIS it drops to 5 (minimum pooled connections I'm guessing?).

I've looked over my code for hours and I don't see any leaks. I close every connection that I open, and any time I catch an error or redirect away from the page I also close the connection there.

I have tried adding .dispose instead of just closing with no changes.

Finally I even modified the connection string to time out pooled connections after 5 seconds and still have way too many open connections.
		<add key="ConnectionStringWF" value="Initial Catalog=Workflow;uid=sa;pwd=password;Data Source=klamath;Network Library=dbmssocn;Connection Lifetime=5;Max Pool Size=5000;"/>


My final "Resolution" can be seen above - I made the max pool size 5,000!!!
I continue to check in SQL how many pooled connections are being used and have seen up to 200+ at any given time and the program is just now being rolled out, I can see use increasing by 300%.
Also, all of these connections are being used by 50 people or less... Usually something like 5-10 at a time.

Questions:
1. Do I need to worry about the number of pooled connections, is having 500 open going to hurt me?
2. Could it be the automatically created connections in ASP.NET causing this? (Creating a sql connection to populate a drop down list or datagridview?)
3. Is there a way to check to see if an already opened connection is available to use?

Going from 3... When I looked in SQL it shows all available connections "sleeping." To me, I think that would mean anyone could use them... But they got error messages - it was like the connection was being reserved.
So I'm wondering if instead of creating a new connection, I could check to see if one was already open.

One of my sub's is below that shows how I check to see if a textbox has changed and if so update it in the database.

Sub Update_Database_Checkbox(ByVal lbl As Label, ByVal chk As CheckBox, ByVal table As String, ByVal Field As String, ByVal PK As String, ByVal PK_Value As String)
		If lbl.Text = "" Then
			lbl.Text = "False"
		End If
		If chk.Checked.ToString <> lbl.Text Then
			Dim db_connection As SqlConnection
			Dim db_command As SqlCommand
			Dim sqlstring2 As String
			db_connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringWF"))
			db_connection.Open()
			db_command = New SqlCommand("Update " & table & " SET " & Field & " = '" & chk.Checked.ToString & "' WHERE " & PK & " = '" & PK_Value & "'", db_connection)
			db_command.ExecuteNonQuery()

			sqlstring2 = "INSERT INTO Update_Log(Date, Emp_Name, Client_ID, Table_Updated, Field_Updated, Field_Before, Field_After, Error) VALUES ('" & Now() & "', '" & label_requester_name.Text & "', '" & lbl_client_id.Text & "', '" & table & "', '" & Field & "', '" & lbl.Text & "', '" & chk.Checked.ToString & "', 'False')"
			lbl.Text = chk.Checked.ToString
			db_command = New SqlCommand(sqlstring2, db_connection)
			db_command.ExecuteNonQuery()
			db_connection.Close()
			db_connection.Dispose()
		End If
	End Sub


Thanks for the help.

Is This A Good Question/Topic? 0
  • +

Replies To: Can't seem to keep sqlconnections closed.

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5076
  • View blog
  • Posts: 13,704
  • Joined: 18-April 07

Re: Can't seem to keep sqlconnections closed.

Posted 01 December 2008 - 06:16 PM

First of all, having a large number of connections sitting there, probably dead can't be good for performance. Same for having a bunch of pooled connections no one is using either.

Secondly, I noticed that you keep creating new instances of the sqlcommand object. Create one instance, use that one instance and change its commandtext to issue different commands. Otherwise you are just burning up resources.

Third, your dispose is synonymous for close.

Lastly, To help you out you can use the "using" statement with your connection which will give you some help from .NET to help make sure your connections are close. Check it out in the example for SqlConnection in the MSDN documentation.

You really should look at making a design where you can have one instance of everything and not being opening and closing connections on every single click of a checkbox etc. Try collecting user input and then when they hit "ok" or something then open up the database connection, make all the changes at once and then closing. Even if you are closing properly that method will reduce load on your server having to constantly open a connection do one small thing, closing, then reopening do one small thing and closing. Opening and closing a connection 15-50 times just for one screen is never good for the system.

:)
Was This Post Helpful? 0
  • +
  • -

#3 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Can't seem to keep sqlconnections closed.

Posted 02 December 2008 - 11:25 AM

I'm not sure I understand how one could open a connection and keep it open.
My code works as such.

User clicks submit.
I then call my function that you see above for each object that I have.
That function checks to see if the data has changed and if so updates the database.

If I open the connection at the initial "submit" - how does my sub that makes the change know that the connection is open?
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 6995
  • View blog
  • Posts: 14,629
  • Joined: 16-October 07

Re: Can't seem to keep sqlconnections closed.

Posted 02 December 2008 - 12:41 PM

I'd disagree with Martyr ( which is rare, come to think of it ;) ). The overhead of creating a connection instance is negligible. It only impacts you when you actually open it, so I wouldn't worry about it. I don't like the business of using an arbitrary sql generator that takes table names, but we'll ignore that for now, it's not your current problem. Also, you should never need connection pooling; it was a bad idea when someone came up with it and still is.

The code you've presented looks fine structurally, mostly... but what if it fails? I suspect you have a bug somewhere that's crashing, not telling you, and leaving the connection open.

The trick is to wrap all your calls with a try finally to make absolutely sure the connection closes.

Here's an example:
' use a global function for connections, easier to maintain
Function GetConnection() As SqlConnection
	' note, if you want to only use one connection instance, you can do something here
	' like I said, not a biggie right now
	Return New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringWF"))
End Function

Sub Update_Database_Checkbox(ByVal lbl As Label, ByVal chk As CheckBox, ByVal table As String, ByVal Field As String, ByVal PK As String, ByVal PK_Value As String)
	If lbl.Text = "" Then
		lbl.Text = "False"
	End If
	If chk.Checked.ToString <> lbl.Text Then
		Dim db_connection As SqlConnection = GetConnection()
		Dim db_command As SqlCommand = db_connection.CreateCommand()
		Dim db_command2 As SqlCommand = db_connection.CreateCommand()
		'bad, don't open so early, open as late as possible
		'db_connection.Open()

		lbl.Text = chk.Checked.ToString
		db_command.CommandText = "Update " & table & " SET " & Field & " = '" & chk.Checked.ToString & "' WHERE " & PK & " = '" & PK_Value & "'"
		db_command2.CommandText = "INSERT INTO Update_Log(Date, Emp_Name, Client_ID, Table_Updated, Field_Updated, Field_Before, Field_After, Error) VALUES ('" & Now() & "', '" & label_requester_name.Text & "', '" & lbl_client_id.Text & "', '" & table & "', '" & Field & "', '" & lbl.Text & "', '" & chk.Checked.ToString & "', 'False')"

		'Now everything is ready, time to talk to the database
		Try
			db_command.ExecuteNonQuery()
			db_command2.ExecuteNonQuery()
		Finally
			' Note, we're not catching the exception
			' just making 100% sure the connection is closed
			db_connection.Close()
		End Try
	End If
End Sub



Another, perhaps more immediate option, since it's a web page...

Use a single global instance of sql connection ( Um, like Martyr said... :P ) Create it on page load event. Close it on the page unload event.
Was This Post Helpful? 0
  • +
  • -

#5 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Can't seem to keep sqlconnections closed.

Posted 02 December 2008 - 01:17 PM

I don't think it is a failing connection since I do use try/catch for any user input (textboxes). However checkboxes have a True/False option so I didn't think an error could really occur here.

You mention connection pooling is bad - can this be turned off?

I'll try the page load vs unload - that may work...
I actually already create the connection in page load! I just close it at the end of the page load...
Would I create the variable as a public variable above the page load - open it in the page load and then close it in the page unload?

Does page unload ALWAYS fire? Whether re-directed to another page or if someone terminates IEXplore.exe process - will the page-unload still fire?

Since the application is now live and working I want to get as much information as possible and then I will slowly re-write each of the pages to be more efficient.
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Can't seem to keep sqlconnections closed.

Posted 02 December 2008 - 01:26 PM

You could also enable MARS (Mutiple Active Result Sets) introduced in SQL 2005. Here is a sample connection string enabling MARS

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;MARS_Connection=yes;



Not sure if this will solve your problem, but it's definetly an avenue to look down
Was This Post Helpful? 0
  • +
  • -

#7 gymratz  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 129
  • Joined: 18-October 07

Re: Can't seem to keep sqlconnections closed.

Posted 03 December 2008 - 01:01 PM

Thank you all!
On the most hit page I decided to re-code it as some suggested.
I created a public variable for the connection.
Inherits System.Web.UI.Page
	Public db_connection As SqlConnection
	Public db_command As SqlCommand
	Public datareader As SqlDataReader


I then only open it twice. Once in the page_load where I populate the page. And again when I click the submit button.

Also in doing this I found two datareaders that I forgot to close that may of also had a problem.
Since doing this my open connections in SQL manager seem to have gone down a lot.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1