what happen I forgot "Conn.Close"

Usually we put close and set=nothing at the end of SQL.
example:

rs.Close
Set rs = Nothing

Conn.Close
Set Conn = Nothing

But I forgot them sometimes.

Question:
What will be affected?
Will server slow down?
How bad it will be?

Has any script that I can use to check my site to find out which pages do not "conn.close or not rs.close"?

Thanks,

 

>>What will be affected?

If you dont close the connection, ASP can't pool the connection so the next time you connect to the database a new connection will be opened.

Opening new connections to the database is one of the slowest processes and it you aren't using stored procedures the whole database read/write is slowed down even more. When you close the connection is frees it to the pool so when new connections are required ASP can use one from the pool (already open and connected) rather than create a completely new one.

Setting the connection to nothing removes the connection from memory so frees up a little memory.

>>Will server slow down?

In theory yes, but if the site isn't too busy you probably won't notice. For a very busy site it will cause major issues. I've seen busy sites and even servers being brought down and when we investigated it was simply because
database connections weren't being closed. OK, these sites were VV busy, but the lesson was learned. As soon as the database scripts were tightened the sites never experiences issues again.

>>How bad it will be?

Site slows down, server slows down, database won't allow any more connections, server crashes. Take your pick, they are all possible.


>>any script that I can use to check my site to find out which pages do not "conn.close or not rs.close"?

Not that I'm aware off. Why not write a connection class and use that for managing connections? When you write the class, add all the clean-up code to it, so that whenever you use this you know the connection is cleaned up automatically. You can find a basic one at http://aspalliance.com/113_Database_Class_in_Classic_ASP but you might want to write your own so you can return data as an array or recordset depending upon the use.

 

Can I all just put the following code as a function at the every pages no matter there is rs.open or conn.open?

<%
rs.Close
Set rs = Nothing

Conn.Close
Set Conn = Nothing
%>

 

You can if you want but you'll probably get ASP errors saying that the object can't be found or something similar. If you've already closed the recordset or connection, you can't close it again since it no longer exists and ASP doesn't like that so you'll get the error.

 

Thanks,

I just added "Close and set Nothing" to my script.

Now it runs better now and not showing old data again.


Cheers

 

>>>>any script that I can use to check my site to find out which pages do not "conn.close or not rs.close"?

You can look in sql server management studio to see unclosed connections - and the details there may help you identify where the problem is.

 

You could use error trapping to do this at the end of every page, like below:

<%
On Error Resume Next
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
%>

Code (markup):

That way if they are already closed or don't exist then execution will continue.
But it isn't hard to ensure all connections are closed after use and is good practice to do so.