Forum Moderators: open
I run the website for my wife's small business.
It has few dozen pages. It started out plain HTML. I migrated to ASP because I wanted to use "includes" for footers, menus, and so on - you know, the boilerplate code I didn't want to have to change in 50 pages, but change in 1 file and have it reflected in 50. There is a guestbook a friend wrote and uses on his own site with no issues - it's based on ASP and Access database. There is an ASP/Access forum from a pretty well known source, and a mailing list signup (a commercial package I paid for) as well.
When I moved pages from HTML to ASP, I simply renamed the files from myfile.html to myfile.asp and added the includes and the forum, guestbook, etc. Made no other changes. It was working for nearly a year, then the ISP upgraded to server2003 and now my site literally crashed once a week, always on Friday/Saturday, and only the ASP pages die, the plain pure mypagetoo.html pages still are displayed. The timing is also suspect - it works on Mondays, Tuesdays, Wednesdays, Thursdays, when it fails, it's almost always on a Friday, or a Saturday. anything with an ASP extension, even if it's just an HTML file with an include in it won't display.
The ISP blames me and my code, I say "it worked BEFORE your upgrade. He says my site is the only one with issues. I ask, then why did it work before the move to 2003?
I found another post here where someone suggested that there was a string needed at/near the top of each ASP page. I have added no such string, only changed the extension of the file and added my "includes". The guestbook, forums and email package were added using the insctructions that came with them and I've not seen anyone complain about them taking servers down or dropping/crashing the ASP service.
I DO have a page that shows the most recent posts from the forum database, so there is some code on it accessing a database, otherwise, all other ASP pages are simply to use the includes and such.
Again, the site worked pretty much 100% before the move by the ISP to Server2003. After than, the ASP service crashes nearly once a week, at times it will go 2 or 3 weeks, again, crashing on Friday/Saturday.
If I'm in the wrong, I want to know it so I can fix it and apologize. If not, I need proof so I can show them what's wrong on their end.
It's a site that is helping to keep my wife's small store alive and without it, we could lose much. We'd be in a big hole if not for the orders this site generates. We ARE desperate.
I'll buy a coffee for anyone who can help! (sent via FAX or email)
Shadows Papa
Could it be that you're getting a little more traffic than before on those days?
The reason I ask is that one of the problems with using Access as your back-end database is that its not really designed for multi-user operations and under load it can sometimes cause the ASP engine to crash.
It could also a code related issue (normally not manually destroying objects that have been created) but you said the code worked before so this is less likely - although I wouldn't rule it out totally.
- Tony
We have had the same problems.... We upgraded to W2K3 Server last week and host a busy site. Currently our dedicated server is crashing about once an hour!
After much searching we beleive we have found the problem. There appears to be a bug with the Microsoft JET Drivers that when placed under demand crashes the system.
As the Access ODBC Drivers use the JET database this is most likely to be the problem.
The server error message that is created each time is:-
"ISAPI 'C:\WINDOWS\system32\inetsrv\asp.dll' reported itself as unhealthy for the following reason: 'ASP unhealthy because 100% of executing requests are hung and 44% of the request queue is full.'."
Here's the error that always appears in the system event log after I've had to manually restart the Web Publishing Service after an 'ASP hang':
"A process serving application pool 'DefaultAppPool' exceeded time limits during shut down. The process id was '2304'. "
You will find that when the server's ASP has crashed it will still continue to serve up traditional html pages but never ASP.
We are now looking to move towards a proper SQL server, this we beleive is the best way forward to permanently resolve the issue.
Good luck in getting this sorted out!
Regards.
Alan.
Shadows Papa
mega-thanks to all who have replied and all who will reply, thanks in advance!
However if it's less critical or you can't afford it you might want to consider mySQL as an alternative, while it lacks some of the features of commercial grade SQL engines it will still more than meet their needs of most non-power users.
- Tony
Shadows Papa
Shadows Papa
Want to be sure of great info before basing a week's work on it.
i agree with txbakers. MSSQL is industrial strength.
if you are running a business, then why not use
a tool that is designed for the job?
your own question points this way, you want to
be sure it works the first time without experimentation.
if you are smaller, there are lot's of hosts who
have packages that include MSSQL, so the license
expense should not be an issue. If the cost is
$20 more a month, do your sales not cover that?
I can't see anything that would suggest your problem being a problem with your setup. What happens when the asp crashes, does it give you an error code?
Shadows Papa
I sell subscriptions to an online database service and with Access it was the same problem you described. With mySQL there has never been a problem.
Access is great if it's read only. The first write or update or delete will start the slow descent into bogdom.
It's not worth the trouble. Access isn't a database server. It's a wonderful little desktop database.
If you don't want to go through mySQL, MS-SQL hosted database access can be easily obtained cheaply ($10 to $20/month). You can still edit the data in Access if you want to do that.
Most of the time, when people have problems with Access, its because they don't optimize their ASP code, their SQL queries, etc. I've seen many problems blamed on Access when the real problem is sloppy coding.
I do agree that if you are doing a lot of updates/inserts then you might want to upgrade, but if you're doing mostly reads, then save your money and optimize your code.
1) Access uses the STA threading model, meaning it's single threaded. This means that you can only have 1 query running at any given time in the database. But these queries only take milliseconds to execute (if you have a small database and it's fairly well designed).
2) Always use DSN-less connections when talking to databases. DSN's use ODBC which is a serious performance bottleneck.
3) Open your DB connection as late as possible in the page. Close it as soon as you can. This frees up resources for other users on the site.
4) More resource management - Make sure you always set those ADODB objects to nothing. IIS garbage collection isn't great...
Hope this helps,
Brandon
if you are running a business, then why not use a tool that is designed for the job?
Amen. Do skimp on the foundation. Access was never intended to be a heavy multi-user platform.
Just because you can doesn't mean you should
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & strDbPathAndName
THAT above is the string I was using to connect. Pretty lame from what I read, unstable, etc.
The below strings WERE available for use, but commented out - here they are:
'strCon = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=" & strDbPathAndName
'strCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDbPathAndName
Needless to say, I removed the Access driver and connected with the OLEDB.4.0 string. We'll monitor and see how it goes. If it's going to crash, it should in a couple of weeks.
Now for those who said to get rid of Access - future development will probably go to mysql. I need to LEARN it first, to play with it, to get confortable and setup some test pages and such. Once that is done, we'll either migrate to it, or ride on Access and when we make major changes, switch from Access to mysql.
In the meantime, and since I don't know much else besides access (and that isn't a great deal) I need to see if Access can't be made to work for now.
Again, thanks for ALL input, suggestions, etc. I've learned a bunch and have taken notes.
There is no better way to learn than from those who've "been there".
Shadows Papa
I know it's the "Jet bug" for sure because I posted the IIS log on an MS IIS newsgroup and one of their techs confirmed it. You can learn about it by searching in Google Groups for 'iis "jet bug"'
Apparently the issue is caused by a "deadlock" happening when two things try to initialize a jet query simultaneously (whatever any of that means).
The MS tech said that it didn't seem to matter whether it was reading or writing: reading is equally likely to trigger it.
Because of the simultaneous co-occurrence that causes it, it is more likely to happen in a high load environment, but definitely can still happen with low load.
I tried everything: tidying my code, reducing unnecessary database accesses, caching database reuslts in application variables; even in desperation I set up an application variable that would only let one script into the database at a time, caused any other ones to wait until the first one had finished.
This all helped (I found some real gremlins in my code along the way) but none of it fixed the problem. Roughly every other night, around 9pm, it all starts going down. (actually, I have to say I think that last crazy idea with the application variable would have to work, I must just not have implemented it right).
So now I have given up, and have a developer moving me over to SQL Server. Apparently it's not very difficult to do if you're database is simple and you only use fairly simple SQL statements. And it's guaranteed to fix the Jet bug for sure. :)
The problems can be 2 fold
1) There is a bug in IIS 6 that causes it to overload due to having 64bit technology built in because someone thought "it would be a good idea". There is a little known fix for this that only few web hosts are privvy to
2) There is also a well known bug in the MS JET drivers that causes database access to lock up. Again MS has beta released a fix for this to the privilidged but not ot the majority
Robbie
[edited by: 4webspace at 7:54 pm (utc) on April 14, 2004]
Here's a question for you Shadows_Papa. Are you in a shared hosting environment?
If you are this problem may be caused by someone else's code and not yours. I've seen the ASP.Dll get completely hosed up when someone is storing afinity threaded com object references at the Session Level and then that object fails... when it does it takes the ASP.dll right along with it until the machine is restarted.
Welcome to WebmasterWorld
Are you going to let us know these fixes? You probably know them, seeing that you own a hosting company.
BTW, URL drops are not permitted, so you may want to edit your post.
Thanks
Robbie
ODBC/DSN connections to Access will cause the Microsoft Access issue become much more prominent. Microsoft advise against ODBC in a hosting arrangement and the fixes they have produced from what I understand do not address ODBC only JET
Robbie
If his host says he's isolated the problem down to a single site you can either take that as the truth or not, but if it gets to the point where you're questioning the technical abilities of your host or their honesty you have bigger problems than a locked process.
He's isolated this site so that it can only affect it, and no other sites can affect it - that's how he determined that whatever it was, it was only with this site, not another causing it.
- Tony
I would ask your host for a iisstate log when the site is in the locked state. If you see a lot of database threads that are deadlocked then its the jet bug.
Robbie
Moved over a few websites that use Access to a brand new more powerful W2K3 server from a W2K server. These websites had functioned flawlessly for a couple of years.
After the move, after periods of time (ranging from 10 minutes to 2 hours) the server would stop serving ASP pages and would only serve static pages until it was restarted.
I was getting the following error in the Application log:
"ISAPI 'C:\WINDOWS\system32\inetsrv\asp.dll' reported itself as unhealthy for the following reason: 'ASP unhealthy because 100% of executing requests are hung and 36% of the request queue is full.'."
I paid for a support incident with Microsoft and after a few days of them looking at the dumps they sent me a BETA version of a fix to the MS Jet.
The file is a beta fixed msjtes40.dll. The server has been functioning fine now for about 24 hours.
Hopefully this will lead you in the right direction.
Until a fix comes along, make sure you destroy ALL connections after you hit Access on a page.
set cnADO = Server.CreateObject("ADODB.Connection")
strDSN = Whatever
cnADO.Open strDSN
blah blah blah
cnADO.Close
Set cnADO = Nothing
(Also do the same for the RecordSet object)
Maybe this will help buy you some time. Good luck.
rubendn has hit the nail on the head with that beta file ;)
Robbie
