4

I have recently become aware of this issue: https://blogs.msdn.microsoft.com/spike/2008/09/17/nested-recordset-and-the-portsocket-in-time_wait-problem-by-example/

Where if you open a recordset, then use the connection for something else with the recordset still open, the web server opens a new port to talk to the DB, then immediately puts that port into the TIME_WAIT state.

I have been going through the site to fix this (by closing recordsets before the connection is re-used - it works) but noticed something odd.

When the page has a "Response.Flush" on it, no matter WHAT you do, it causes an extra port to be used and then be put into the useless TIME_WAIT state. This can lead to a serious case of port exhaustion.

SAMPLE CODE:

 <%@ Language=VBScript %>

 <html>

<head>

</head>

<body>
<%
response.flush 

set cnn = server.CreateObject("adodb.connection")

CNN.cursorlocation=3
cnn.open [YOUR CONNECTION STRING]

set rs=server.createobject("adodb.recordset")
set rs=cnn.execute("select top 1 * from [YOUR TABLE]")





cnn.close
set cnn=nothing
%>


</body>
</html>

To check for time waits you can run:

netstat -nao | find /i "[YOUR DB IP]" /c

via the command prompt on the web server. Assuming this is a test system, you should immediately see time_wait connections popup from your web server to your DB server. Remove the flush, and this stops.

Googling has not helped - open to any suggestions.

Environment: IIS 7.5, Classic ASP, SQL Server 2008.

EDIT:

I also tried this as per the comments:

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  'No need to handle connection let ADODB.Command create and destory it.
  .ActiveConnection = sqlcnnstr1
  .CommandType = 1
  .CommandText = "select top 1 * from [YOUR_TABLE]"
  Set rs = .Execute()
  If Not rs.EOF Then data = rs.GetRows()
  Call rs.Close()
  Set rs = Nothing
End with
Set cmd = Nothing

Still same issue. With the flush, extra connection, without, same connection.

EDIT 2 Turns out its got nothing to do with database. If you have JUST a response.flush, nothing else, that already causes an extra connection, from IIS to the client, so I've been looking at the wrong of things.

The question now becomes can you get away with not having response.flush spawn an extra connection from IIS to the client.

Shalom Slavin
  • 93
  • 7
  • Never come across this but I guess that what happens when you don't use your db connections efficiently, properly closing off resources not holding on to open connections, freeing memory etc. – user692942 Aug 15 '16 at 22:24
  • I don't see how I can do the above more efficiently - open to any suggestions. Whatever I try doesn't seem to alleviate the port issue unless I remove response.flush – Shalom Slavin Aug 16 '16 at 13:54
  • It's very simple, stop leaving the `ADODB.Connection` open for the length of the page. A common approach is to use `ADODB.Command` when executing a query, which allows you to specify the connection string which when the Command object is executed creates the Connection object and opens it for you. In combination with this you can use the `GetRows()` of the `ADODB.Recordset` to convert it to a 2 dimensional array which doesn't require an open connection to the database. – user692942 Aug 16 '16 at 15:23
  • Some examples - http://stackoverflow.com/a/26278209/692942 / http://stackoverflow.com/a/35735315/692942 / http://stackoverflow.com/a/22305896/692942 – user692942 Aug 16 '16 at 15:28
  • Some of that seems useful but I don't know if it applies here. In this example the flush seems to be the sole contributor to the time_wait port. The only thing I can try from those links is destroying the recordset which I just tried - no change. The array idea doesn't apply since I'm not even touching the recordset in my example so adding the array would really just make this heavier. – Shalom Slavin Aug 16 '16 at 18:51
  • Heavier, using a connected `Recordset` object is heavy enough, closing that and using an `Array` is far more efficient. The issue you have arises from trying to maintain connection during a `Response.Flush`, this method while not explaining why will negate the need for it. – user692942 Aug 16 '16 at 19:48
  • Destroying the Recordset isn't enough as the `Connection` object will remain. – user692942 Aug 16 '16 at 19:53
  • Is what I'm doing in the sample not destroying the connection? What else can I do to it? – Shalom Slavin Aug 17 '16 at 00:50
  • Yes but in that example the connection remains open until the conclusion of the page which is an unnecessary overhead. Whereas the method suggested only hold the database connection until the `ADODB.Command` object finishes executing. Any use of the returned data is handled by the `Array` which doesn't use an active database connection. – user692942 Aug 17 '16 at 07:43
  • Ah interesting - I actually didn't realize you can let the command manage the connection - I'll see if that changes anything. – Shalom Slavin Aug 17 '16 at 13:48
  • No luck...will update the question though to show what I tried. – Shalom Slavin Aug 17 '16 at 13:56
  • In your updated example where do you try the `response.flush`? – user692942 Aug 17 '16 at 15:02
  • Right at the beginning (not a real life example but it exaggerates the point). After <%. If you mean in the one with the command object, same, I replaced the cnn.execute with the command, the flush still happens right at the beginning. – Shalom Slavin Aug 17 '16 at 16:08

1 Answers1

1

Turns out the TIME_WAIT connection had zero to do with the database, just having a response.flush opens a new connection from the web server to the client, and that's the connection that results in TIME_WAIT connections. I have not found a way around it and therefore am just limiting response.flush for special circumstances, otherwise not flushing.

Shalom Slavin
  • 93
  • 7