ADO connections do they clean up by themselves?

[Janette]

Hi,

Can anyone tell me, if you were to open a connection to a database at the
top of each ASP page in a website, for example like this

<%
dim dbconn

set dbconn=server.createobject("ADODB.connection")
dbconn.ConnectionString= "File name=c:\databaseconnection.udl"
on error resume next
dbconn.open
if err.number <> 0 then
log_errs "Can Not Connect to database",""
Response.Redirect whome & "/closed.asp"
end if
on error goto 0

%>

use the connection, but never close the connection at the end of the page,
what will happen? Ie, how long will the database connection stay open? Is
the connection to the DB automatically closed when you leave the page, or
does it leave the connection open but idle, and therefore up to the
individual DB to eventually timeout and reuse the connection? Does it
potentially cause a memory leak, because you end up with lots of unused
connection and recordset objects that are no longer in use? Would it cause
sleeping sessions in a SQL Server database?

I realise it is preferable always to close connections and recordsets and
set them = to nothing when you have finished with them. But I really need to
know what the implications are if this doesn't happen.

Thanks in advance.
Janette

 

[Luke Zhang [MSFT]]

Hello,

When you create an ADO connection in an ASP application, the ADO connetion
object will be created in IIS' process. If you don't close it and set it to
nothing, it will leave idle in the process for a while (depends on IIS).
For ADO record set, there are server side recordset and client side
recordset. Client side recordset will also leave in the IIS process as ADO
connection. So, it is risk to leave many connections or recordsets in the
an ASP application without close them.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Luke Zhang [MSFT]]

Hello,

How is it going on now? Is the information I provided in the previous
message helpful? If you need further assistance on the issue, please feel
free to let us know.

Sincerely,

Luke Zhang

Microsoft Online Community Support
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Janette]

Hi Luke,

There is another thread which is providing good information. I did some
research and found that Oracle connections don't remain open very long, even
if you don't close them. But I am yet to test SQL Server. But, I still don't
know if it does cause memory leaks and how to detect it if it is. Let alone
what the symptoms of a memory leak from an IIS perspective would be.

Regards
Janette

 

[Luke Zhang [MSFT]]

Hello Janette,

To detect a Memory Leak, we can use the counters in System Performance
Monitor, here is an article about this:

http://support.microsoft.com/?kbid=325558

SQL server also provides some System Performance counter, which can help us
determine the connection number and status of a Server SQL database.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Janette]

Hi Luke,

This webcase about detecting memory leaks is good. The only thing is, if I
think an ASP application might be causing a memory leak, how do I know which
process this would be on the server. I gather that IIS runs several dllhost
process which are the IIS processes. Is there a specific ASP process?

Regards
Janette

 

[Luke Zhang [MSFT]]

Hello Janette,

To watch a specific ASP application's memory usage, you may:

1. Configure the new virtual folder to run in High Isolation - Out of
Process , so that the ASP application runs under a separate copy of
Dllhost.exe.
2. From your browser, run asp application.
3. To view memory usage, start Windows Task Manager and click the
Processes tab. One instance of Dllhost.exe shows linear growth in memory
usage, corresponding to the memory leak.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.