We are using a dedicated Windows 2003 machine running IIS 6.0 and SQL Server 2005 (on the same machine). Our code is Classic ASP and recently our server became unresponsive during busy hours where only about 50 requests would execute (with long delay) and the rest would time out. For the site visitors the experience is a hung service with no response.
We want to make sure that there is no memory leak driven by our code and we've checked line by line to make sure that we are closing all connections and destroying objects. However, we also would like to validate the method we connect to the SQL database and would like the help of the community here. Below is the code we are using to connect and would appreciate if someone can skim over it and tell us we we should be doing something differently:
<%
Function glpCloseRst(objRst)
If IsObject(objRst) Then
If objRst.State = 1 Then
objRst.Close
Set objRst = Nothing
End If
End If
End Function
Function glpCloseDBConn (objDBConn)
If IsObject(objDBConn) Then
If objDBConn.State = 1 Then
objDBConn.Close
Set objDBConn = Nothing
End If
End If
End Function
%>
<%
ConnectionStrSQL = "Driver={SQL Native Client};Server=555.55.555.55;Database=dbname;Uid=username;Pwd=password;"
set connSQL = CreateObject("ADODB.Connection")
connSQL.open ConnectionStrSQL
%>
<%
Dim allSQX
allSQLX = "SELECT * FROM TABLE WHERE id='something';"
Dim allRsX
Set allRsX = Server.CreateObject("ADODB.Recordset")
allRsX.Open allSQLX, connSQL, 3, 3
DO UNTIL allRsX.eof
...somestuff...
allRsX.movenext
LOOP
glpCloseRst(allRsX)
glpCloseDBConn(connSQL)
%>
many thanks