sql server running out of connections? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

sql server running out of connections?

Author

Topic

netean
Starting Member

1 Post

Posted - 2002-03-26 : 06:14:40

got a problem with a couple of pages on my site. its' written in asp.net and seems to create several connections for each page visited.

these connections don't then seem to be reused or dropped and as more users (i.e. just 2 test users at the moment) read the page, it creates more connections until of course the maximum number of connections is reached and the site falls over.

On a couple of pages (more complex pages with several database calls and several user controls) each access to the page creates between 7 and 12 connections, which aren't subsequently closed.

I have scoured the code and ensured that every connection is closed, every connection is disposed of (with the dispose() method) and variables are set to "nothing".

(I've tried removing the multiple dataase connections I have in the page and replacing them with one that it's opened in page_load and closed in page_unload)
I've tried creating a garbage collector for the page_unload that closes connections.
I've tried everything else I can think of without any luck.

I'm pulling my hair out now, as I can't track down the problem:

Do user controls (with code behind in them that calls databases) automatically create extra connections by default?

Has Anyone got any suggestions at all: anything I can look at, anything I can try at all?

This site I'm working on is going to have possibily 1000s of concurrent users and at the moment falls over with just 2 users in about 5 minutes.

Any suggestions whatsoever would be greatly appreciated at this point!
---------------------

FYI:

example:

Login page: user enter details (username and password)
page posts back to itself and opens a connection to check they are valid.
if they are, it looks at the database for anything in their "shopping cart" if there is something there it redirects to the "view cart" page

if not shows their current user profile.

the login page uses a code behind file, and there are approx 5 user controls on that page (3 of which also have their own code behind file)

--EVERY database connection is closed in the code
with a subroutine like so and I've trawled thru the code and verified that every opened connection is closed:

Private Sub CleanUpTime(MyCommandObject As SQLCommand)
MyCommandObject.Connection.Close
MyCommandObject.Connection.Dispose()
MyCommandObject.Connection = Nothing
MyCommandObject.Dispose()
MyCommandObject = Nothing
End Sub

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-26 : 07:54:19

You're not alone...jackstow is also having a similar problem:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14172

I don't know if he's solved it yet, but he might be able to tell you what doesn't work.

I have a sneaking suspicion that passing an object to a sub or function passes a copy (byVal), not the actual object (byRef). I vaguely remember reading something about that recently, but I don't know if it applies to .Net. If that's true, then the central cleanup subroutine wouldn't help; it might even be causing the problem.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-26 : 08:08:32

Well I'm looking at netean across the office at this very moment so I'd tell him if I knew!!

quote:

I have a sneaking suspicion that passing an object to a sub or function passes a copy (byVal), not the actual object (byRef).

Several test pages have been created that use the same style of code without recreating the problem so I don't think it is that - stangely an ASP website is showing the same problem now...
This looks suspicious to me - http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235282 - but don't know if it is relevant to .NET
Also, the multiple connections that were created took over an hour to disappear from sp_who2 - more strangeness..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-26 : 08:38:39

quote:
Well I'm looking at netean across the office at this very moment so I'd tell him if I knew!!

That makes it easier than having to call Femenella with a problem!

Just for my own curiosity, are you guys doing this:

- Close each recordset prior to opening another on the same connection.

It's in the MS bulletin you posted. If you ARE, and it's still happening, I think you've found a bug with ADO.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-26 : 08:57:11

Well there is no Recordset concept in .NET, you have server controls like a datagrid that you bind to datasources, all quite different. But, yes, all the objects are being closed down and disposed of. I can feel a major rewrite coming on...

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-26 : 09:03:35

Are you using SqlDataReaders or building datatables? Are you using the CommandBehavior.CloseConnection option in your ExecuteReader method calls?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-26 : 09:21:20

It's being done like this;

Dim Connection As New SQLConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim objCmd As SQLCommand
objCmd = New SQLCommand()
objCmd.Connection = Connection

objCmd.CommandText = "SelectInfo"
objCmd.CommandType = CommandType.StoredProcedure

objCmd.Connection.Open()
SCDatagrid.DataSource = objCmd.ExecuteReader()
SCDatagrid.DataBind

CleanUpTime(objCmd) 'this runs the sub posted by netean

.. so all the connections and command objects should be closed and destroyed. This syntax does work for the test pages we have created which create only one connection in the pool as you would expect.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-03-26 : 12:56:53

Ahk don't use a DataReader for data binding ... bad bad bad ... DataReader's require the connection to be open and unless you use Page_Unload() event to disconnect the datagrid then I think it leaves the connection open. Try using a DataSet instead. This may fix your problems since all data is transfered to the web server vs the datagrid having to ask for each row at a time with the datareader .... just a thought...

Edited by - onamuji on 03/26/2002 12:58:32

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-26 : 13:54:34

How large is your connection pool?

setBasedIsTheTruepath
<O>

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-26 : 14:38:19

quote:

Ahk don't use a DataReader for data binding ... bad bad bad ... DataReader's require the connection to be open and unless you use Page_Unload() event to disconnect the datagrid then I think it leaves the connection open. Try using a DataSet instead. This may fix your problems since all data is transfered to the web server vs the datagrid having to ask for each row at a time with the datareader .... just a thought...

This is incorrect. A connection is only maintained for the duration of the page creation process. Or until you close it, whichever comes first. SqlDataReaders are much faster than a DataSet. They implement the "firehose curor" using a native TDS datastream. Here's how I would write this:

Dim Connection As New SQLConnection(ConfigurationSettings.AppSettings("ConnectionString")) 

Dim objCmd As SQLCommand 
objCmd = New SQLCommand() 
objCmd.Connection = Connection 

objCmd.CommandText = "SelectInfo" 
objCmd.CommandType = CommandType.StoredProcedure 

Dim x1 as New SqlDataReader

x1 = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

objCmd.Connection.Open() 
SCDatagrid.DataSource = x1 
SCDatagrid.DataBind 

x1.Close()

My syntax may be a little off. I do all my .NET coding in C#. Using CommandBehavior.CloseConnection will close the connection when the SqlDataReader is closed.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-27 : 03:56:50

I think that's dead on the money graz, the only slight change I would make would be to do this to close it off;

objCmd.Connection.Close
objCmd.Connection.Dispose()
objCmd.Connection = Nothing
objCmd.Dispose()
objCmd = Nothing

..found out recently that the dispose() method should be called if its there - so much for .NET's 'garbage disposal'. But it's all personal preference in the end I think. Anyway, I think I have tracked it all down to a function in a user control that opened a connection, ran a proc, returned a datareader and closed the connection - only thing is the returned datareader still seems to have a connection associated with it.. Going through the code now and the connections seem to be under control. Hurrah!

setbasedisthetruepath - the default ADO.NET connection pool is min 0 max 100 - pretty easy to change from web.config as well - before I found the bug the application was eating them up pretty fast though.

Thanks for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources