| Author |
 Topic Search  Topic Options
|
sfd19 
Senior Member
Joined: 20 December 2004
Status: Offline
Points: 263
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Compacting Access DB on server
Posted: 18 February 2005 at 7:05am |
When compacting an Access DB on the server with an ASP script, does the database get automatically locked?
Or does read-write access to the DB still exist in that moment and could a database become corrupted when:
1. Compacting process starts
2. A field gets altered (like edit post) or a new entry is made (like new topic or new reply)
3. Compacting process ends
I checked quite some Access/ASP tutorials concerning the compact process but could not find anything regarding my question.
I am asking this since we are using a script that automatically compacts the DB once each week (and then zips the uncompacted DB to make it faster for download), the DB is WWF/EzASPSite.
Anyone?
|
|
|
 |
dj air
Senior Member
Joined: 05 April 2002
Location: United Kingdom
Status: Offline
Points: 3627
|
Post Options
Thanks(0)
Quote Reply
Posted: 18 February 2005 at 7:33am |
|
from what i have heard it can corrupt a database.
and thats why its best to close the forum during this action. and then reopen the forum afterwords.
|
|
sfd19
Senior Member
Joined: 20 December 2004
Status: Offline
Points: 263
|
Post Options
Thanks(0)
Quote Reply
Posted: 18 February 2005 at 7:53am |
Thanks.
That is also what I heard, but does there exist a final answer to this?
I can not believe that I am unable to find an offical confirmation whether an Access DB gets locked or not during the compacting process anywhere. 
Otherwise I would be forced to lock the DB at the beginning of the compacting script and to unlock it at the end.
|
|
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 18 February 2005 at 8:08am |
|
The database should be locked as it is connected to through ODBC.
Every time an Access database is connected to through ODBC the database is locked and can only be access by that user.
However, compacting and repairing of the database can sometimes, un-corrupt or even corrupt a database.
Access database are always temperamental (they suck) and can get
corrupted very easily loosing all data so you should always make a
backup first before doing anything.
I would suggest making daily backups of any Access database, unless it is only accessed very rarely.
Edited by -boRg- - 18 February 2005 at 8:09am
|
|
|
|
sfd19
Senior Member
Joined: 20 December 2004
Status: Offline
Points: 263
|
Post Options
Thanks(0)
Quote Reply
Posted: 18 February 2005 at 10:15am |
The compacting script of course makes a backup of the DB before doing anything else. Additional backups are made 4 times a day since our site gets accessed very often. (300.000+ hits a month)
 -boRg- wrote:
The database should be locked as it is connected to through ODBC.
|
In order to make sure that "should be" is really an "is", I will create a 2 GB Access DB and then delete 50% of the content by random. Compacting such a DB should take long enough to test what will happen when I try to post a new message while the compacting process is still running.
I will post the results here.
|
|
|
|
sfd19
Senior Member
Joined: 20 December 2004
Status: Offline
Points: 263
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 February 2005 at 3:53pm |
|
The compacting process for the 2 GB Access database took 40 minutes, that gave plenty of time for testing.
I got 6-7 different error messages when trying to access the DB while
the compacting process was still in progress, all of them clearly
indicating that the DB was locked by a single user and that it is
inaccessible. It did not matter whether I tried to write to the DB or
to read from it, the DB was completely locked by the compacting process.
Therefore I would say that you do not need to close the forums while
compacting, the DB is inaccessible anyway. Also I doubt that the DB
could get corrupted because of DB access while compacting since it is
locked. That the compacting process itself could corrupt a DB is
another story.
|
|
|
|
Gullanian
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 February 2005 at 4:11pm |
|
Just looked at your site, probably would want to think about upgrading
database because you have well over 50,000 posts. Thats a lot of
data to load into memory in one go per page call.
|
|
sfd19
Senior Member
Joined: 20 December 2004
Status: Offline
Points: 263
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 February 2005 at 5:06pm |
Thanks, I know that Access sucks with bigger forums but for now we have to stick with it.
sfd19 wrote:
Thanks Borg, though I am absolutley aware of all the limitations of an Access DB.
But we have heavily altered much of the original WWF/EzASPSite code, have added lots of features, files, tables, databases and more. I doubt that there exists a file that has not been altered at all.
The time consumption for making the entire code SQL-ready would be enormous.
What I did was creating an archive where all older topics get moved into. That reduced the original size of the DB from 130 MB to ~60 MB, and with that feature I can keep the 'live' DB size at 50-70 MB while the archive DB size does not matter so much, since it gets not accessed frequently. Also the archive DB can not get corrupted since it is read-only and changes are only made every few months when I am archiving more topics. That also makes a frequent backup of the archive unnecessary.
SQL would of course be the way to go, but not only the time for all the code changes but also the the money that would be required to host it on SQL/ASP-webspace (15+ GB traffic, 750+ MB webspace, at least 200 MB SQL) makes it impossible.  |
Edited by sfd19 - 21 February 2005 at 5:06pm
|
|
|
|
