Compacting Access DB on server
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=13877
Printed Date: 30 March 2026 at 4:18am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Compacting Access DB on server
Posted By: sfd19
Subject: Compacting Access DB on server
Date 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? ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
Replies:
Posted By: dj air
Date 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. |
Posted By: sfd19
Date 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. ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
Posted By: WebWiz-Bruce
Date 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. ------------- 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: sfd19
Date 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)
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. ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
-boRg- wrote:Posted By: sfd19
Date 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. ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
Posted By: Gullanian
Date 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. |
Posted By: sfd19
Date 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. http://forums.webwiz.net/forum_posts.asp?TID=13870&KW=sfd19&PN=0&TPN=1 - http://forums.webwiz.net/forum_posts.asp?TID=13870&KW=sfd19&PN=0&TPN=1
------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
Posted By: Gullanian
Date Posted: 21 February 2005 at 5:29pm
| It shouldn't take too long to change any site to be SQL server ready, 90% of transfer should go problem free. |