Site Hanging on Occasion

[Aamin]

I have a site that I maintain for a bookstore and recently it hangs whenever users try to access the home page or any other page. The browser page goes white and it seems as though its trying to find the page but it never does. I believe there is something wrong on the server side but my ISP insists that its programmer error. When they reset the server the site works fine but then it goes down again after a few days. Any ideas??

 

[Aamin]

I talked to the ISP and they concluded that the code that creates the DB connection and recordsets have to be modified. I examined the code again and found a couple of files that I suspect may be the prb. The following is from an include file <adovbs.inc> that is included with each page:
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
.....

The following is from a file <UserSession.html> that is included on each page:
...
'CHECK TO SEE IF THE SESSIONID HAS BEEN SAVED TO 'CUSTOMERS DATABASE'
'IF NOT THEN ADD IT
'IF IT IS PRESENT THEN MOVE ON
sessRS.Open "Select * From usersession where sessionID = '"&thisnewsessionid&"'", sessConn, adOpenDynamic, adLockPessimistic, adCMDText

if sessrs.eof then

sessRS.AddNew
sessRS("sessionID")= myusersessionid
'sessRS("userdate")= date()
'sessRS("usertime")= time()
'sessRS("system")= thishost

sessRS.Update
sessRs.close

sessConn.close
set sessrs = nothing
set sessconn = nothing

end if
...

The prb i have with the above code is that if the session varibale exists the Conn does not close. Can this be a possible cause for the prb?

 

[Sheco]

Just move the end if up a few lines so that it is between sessRs.close and sessConn.close.

That might be the problem but does this ISP have any logs that show some problem with the memory usage of ADO or IIS or something? How did they decide this was the problem?

 

[Aamin]

No, they did not provide me with any logs. They said that the server that the site is on has other sites on it as well and that those sites never have a prb when this site goes down.
They said that i should try using DSN instead becuz it has a built-in Conneciton Pooling element that may help the prb. What do you think?

 

[Sheco]

Connection pooling is where the connection stays open even after you close it so that when you ask for it again it won't take as long to connect.

I don't see how that would help if the problem is that you already are not closing the connections.

And what does a DSN have to do with this? I don't know unless what they are really trying to say is that you should use the OLE DB driver so that connection pooling will kick in... but you can use OLE DB with a regular ADO ConnectionString.

Also connection pooling can only happen between database connections using the same username/password combinations... this may or may not be an issue for you.

The good news is that the suspect code is in an INCLUDE file so that you only have to change it once rather than plodding through each page in the site searching for it.