Database System Resource Overload
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=20353
Printed Date: 09 April 2026 at 2:10pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Database System Resource Overload
Posted By: godot
Subject: Database System Resource Overload
Date Posted: 19 June 2006 at 10:11am
|
Hi,
My Forum has been running rather smoothly until I encountered this problem:
Server Error in Forum Application
An error has occured while executing SQL query on database. Please contact the forum administrator. Support Error Code:- err_Access_get_forum_data File Name:- default.asp Error details:- Microsoft JET Database Engine System resource exceeded. I highlighted the problem to my service provider and the reply was:
This error might be caused by many reasons. Most of the time it is caused by a constantly opened connection to the database or an opened Recordset which consumes a lot of resources might generate this issue. Please try to go through your code to see if all the connections or recordsets are closed properly and the references to them have been set to Nothing after they become useless. Also, we can try to open the Recordset with less resource consuming cursor types. For example, open it with a dynamic cursor will be better.
We suspect one or more of the sites in the server are having this problem. We would appreciate your help by checking thru your scripts to minimize the problem. We will also investigate to pin-point other sites that are having this issue.
Can I ask if the code in version 8 is robust and does not have the issues raised by the service provider?
Thanks.
|
Replies:
Posted By: WebWiz-Bruce
Date Posted: 19 June 2006 at 10:54am
|
I can 100% confirm that the code in version 8 is totally robust and all recordsets and database connections are closed the the objects released as soon as they are no longer required. Months of time was spent on version 8 optimising each and every database query to use less server resources and for performance, making Web Wiz Forums one of the most optimised and low resource bulletin board systems presently available. Web Wiz Forums has even gone further and uses the web servers memory to cache common database queries to cut down on server and database resources. Array's are used to store database quires instead of recordsets improve performance further and cut down on server resources. With the SQL Server version so called 'Dirty' database reads are performed whereby the database table is NOT locked during reads, and in most cases only row locks are used when the database is updated. You can assure your web host that the code is very optimised so the fault would not be in the forum code. If your forum gets allot of traffic and posts then it looks like your host needs to allocate more resources to the SQL server as bulletin board systems, by their very nature, do require allot of database reads. The error you mention often happens if the SQL Server is running out of memory, your host maybe trying a slightly underhand tactic of blaming users code, rather than go to the expense and trouble of upgrading the SQL servers hardware. This is often the case with shared SQL Server environment were the host places as many databases on the 1 SQL server as possible to maximise revenue. The amount of resources that Web Wiz Forums requires, the forum on this site can run on MSDE, with over 100,000 posts, 9,000 members, and 30 to 100 active users at all times. MSDE is a free cut down version of SQL Server that only allows 5 concurrent connections, and is further throttled. This really shows how low resources intensive the Web Wiz Forums optimised code really is. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: godot
Date Posted: 19 June 2006 at 12:44pm
|
Thanks, boRg for the prompt reply. I suspected that it was not the fault of the code but something else.
I think the server is either unstable or it lacks of memory. All these point to the need to upgrade the servers as more databases are been added. The servers might not be able to handle the traffic it seems.
|
Posted By: dpyers
Date Posted: 19 June 2006 at 7:07pm
|
The causes raised by your host are valid for MS Access data bases... but it's a system-wide resource and the problem may be caused by some other site. As borg stated though, all db connections in the standard forum are closed and resources freed up. If you have mods, you may want to check them though. I've seen some open connections left by mods. Otherwise, the problem is with some other site. Bad MS access code is the biggest single cause of IIS instability. ------------- 
Lead me not into temptation... I know the short cut, follow me. |
Posted By: SMR Group
Date Posted: 20 June 2006 at 5:00am
|
Access is supposed to handle up to 255 concurrent connections, and that can mean a lot of users... provided not many rows are involved and everyone is using code that closes each session the moment it's not needed, as Web Wiz does. If you're using a shared Access database then a) the number of connections can quickly get soaked up and b) if somebody else's code "forgets" to close the query you're done for.
The Jet engine makes things worse as it hogs even more system resources. The service provider's explanation is valid, but relates to all their users.
Solution... switch to MySQL or similar, something that was actually designed to run on a server.
|
Posted By: WebWiz-Bruce
Date Posted: 20 June 2006 at 12:32pm
|
The 255 connections is Microsoft theoretical amount, in reality this is allot less and with a web server you usually go through the connection pool which only has 20 connections available for Access. Access is also a desktop flat office file and not really up to anything in a web environment other then small database access very infrequently. Microsoft have finally realised this, and this is why they have come up with their free SQL Server Express 2005, based on SQL Server architecture, but free, and you can attach SQL Servers MDF files to it making it as simple as Access to use. However, as it is relatively new it may take web hosts sometime to catch onto this and offer it as an alternative option to Access. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: godot
Date Posted: 23 June 2006 at 9:03am
|
Thanks guys for the insight. My service provider has since opted to increase the virtual memory in the server.
I suppose since I'm on'shared-hosting', I will have to live with the possibility of other users with 'bad scripts' affecting my uptime.
Anyway, I'm wondering if migrating my files from the present Win 2000 server to Win 2003 will help boost performance. But I was warned that Win 2003 works very differently from that of Win 2000 - and they will not be in a position to help 'tweak' or configure the scripts if necessary. My questions are:
1) Will migrating my present Forum Ver 8 require any tedious tweaking and configuration? I hope there isn't a need to tweak anything actually.
2) Is it worthwhile, justifiable to migrate to the 2003 server?
Appreciate your views and advice.
|
Posted By: WebWiz-Bruce
Date Posted: 23 June 2006 at 11:55am
|
If they are having to increase the virtual memory on the server it must be well overloaded as the virtual memory should only be used after the physical memory runs out. Windows 2003 server isn't that much different to Windows 2000 when it comes to running ASP and Web Wiz Forums runs exactly the same on Win 2003 as it does on Win 2000. The way IIS works on Windows 2003 is a vast improvement over Win 2000 and is allot more stable, with sites with bad scripting less likely to take down the whole web server. The performance is also better. The only tweaking you may need to do to Web Wiz Forums is updating the database path to the Access database if it is in a different location to the present server. Web Wiz Guide offer it's own hosting on Windows 2003 servers at, http://www.webwiz.net - www.webwiz.net , and also offer mySQL databases for Web Wiz Forums, with Web Wiz Forums installed for you. We are planning on doubling the bandwidth on all accounts over the next month as well as offering mySQL across all plans as it is much better than Access which often courses issues. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: Pugnacious
Date Posted: 25 June 2006 at 1:25am
I completely agree -boRg-, I manage over 100 windows 2003 servers daily, and I've got to say that the system is a large improvement over 2000 server. You say web wiz guide hosting is expanding, by any chance are they looking for new employees? lol |
-boRg- wrote:Posted By: WebWiz-Bruce
Date Posted: 26 June 2006 at 10:03am
|
Not at the moment, on the web hosting side, as the Windows 2003 boxes need so little attention that it only takes up a few hours, at most, of my time each month administering the windows 3003 servers once the server is setup. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: Pugnacious
Date Posted: 26 June 2006 at 7:39pm
Well if you ever need help, give me a shout. I'll send you a resume. 
|
Posted By: WebWiz-Bruce
Date Posted: 26 June 2006 at 8:58pm
|
Not unless you can work for free, the web hosting doesn't even cover the cost of servers for the the web sites already hosted and the dedicated server required for this site.
------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: Pugnacious
Date Posted: 26 June 2006 at 9:00pm
| It's a posibility. I'm skilled in remote management, and it would look good on my resume. |
Posted By: godot
Date Posted: 27 June 2006 at 3:57pm
|
I've migrated the files to a win 2003 server and guess what?
As expected, there is problem with the mapping of the pathway.
The new server does not seem to allow the " ../ " command for the virtual pathway. Did I miss out something here?
I tried using the physical directory and guess what? It worked.
But because I have other scripts using the virtual path, i still encounter errors and can't get them to work. The alternative would be of course to make changes to the script so that it will use the physical path but I don't know how ...
Example:
Set adoCon = Server.CreateObject("ADODB.Connection") connstr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../../db/XYZ.mdb")adocon.Open connstr If I were to modify it to use the physical path, what do I do with the above section of the script? Or am I too presumptious to just change this section? Help! |
Posted By: WebWiz-Bruce
Date Posted: 27 June 2006 at 4:19pm
|
Remove the part that says:- Server.MapPath Then just change it to the physical server path. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: godot
Date Posted: 28 June 2006 at 11:32am
|
Set adoCon = Server.CreateObject("ADODB.Connection") connstr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../../db/XYZ.mdb")adocon.Open connstr Taking away & Server.MapPath Do you mean the edited script should read something like this:
Set adoCon = Server.CreateObject("ADODB.Connection") connstr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="C:\webspace\XYZ.com\db\XYZ.mdb" adocon.Open connstr |
Posted By: WebWiz-Bruce
Date Posted: 29 June 2006 at 7:47am
|
Yes that is correct
------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: godot
Date Posted: 29 June 2006 at 3:22pm
Thanks, boRg for the help.
I did not change the scirpt after all as the host provider enabled the parent path. Didn't know that in Win 2003 environment, the ../ command is not supported and you have to 'enable the parent path'.
I suppose the example of the edited script:
Set adoCon = Server.CreateObject("ADODB.Connection") connstr="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="C:\webspace\XYZ.com\db\XYZ.mdb" adocon.Open connstr is applicable to all situations when we map databases using the physical path.
|
Posted By: Mikey
Date Posted: 29 June 2006 at 9:18pm
As far as i remember remove the " in red. ------------- Handyman man? |