Microsoft Access - compact and repair multi-user databases
Can anyone suggest a good strategy for doing a regular compact & repair for multi-user databases? I'd like to do one perhaps daily but at least weekly. However, a lot of the users leave themselves signed in to the database overnight so getting exclusive use of it virtually never happens.
My only idea is to use code to kick out the users at, say, midnight and then do a scheduled compact & repair at 1 AM or something. But before I implement that, I was curious if there is a better approach.
Thanks in advance!
My only idea is to use code to kick out the users at, say, midnight and then do a scheduled compact & repair at 1 AM or something. But before I implement that, I was curious if there is a better approach.
Thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd urge you to implement both
This would at least give you back some control. Once this is in place, then you can simply use a Scheduled Task to Compact the database as you see fit.
- http://www.rogersaccesslib
rary.com/f orum/topic 327.html - http://www.rogersaccesslib
rary.com/f orum/topic 328.html
This would at least give you back some control. Once this is in place, then you can simply use a Scheduled Task to Compact the database as you see fit.
There is also the option of closing the connection to the Open Files from your BE ...this is done at OS level .
There is also the option of closing the connection to the Open Files from your BE ...this is done at OS level .True, but should only be done as a last resort, when you are sure everyone is out, not actively using the database. This is useful with hung processes/handles.
@Daniel..trust me on this...it wouldn't matter... whatever you do there will always be some forgetful user...but if he/she looses some work a couple of times he/she will think twice before leaving the application open and leave for the weekend...and if my memory serves me right you can always read the computer that holds the lock and notify if you believe that it would any chance of getting a "response"
<<it wouldn't matter...>>
Sorry John, but Daniel is correct. Anytime you force a connection closed with a JET/ACE Back end, you run the risk of corrupting the database.
It's not a matter of the user just loosing work, but actually having the database messed up and needing a compact and repair.
With that said, killing off a connection in the middle of the night more often then not won't hurt as most users would be idle. But if they have a long running task going, then forcing a disconnect is the last thing you want to do. You are far, far better off to having an inactivity timer and a forced shutdown and lockout built into the app.
And a bigger risk is backup strategy's that simply copy the DB file, which you can do, but absolutely should not. If users are in and writing, you have a very good chance of getting a DB that is inconsistent.
Of course if your using something other than JET/ACE, then that's a different story and you can close a connection without fear. Then it will be just the user that looses work. Still, not a smart thing to do though. Have a multi-step process that's not in a transaction and you could end up with data inconsistencies.
Far better off to have something built into the app to get users out for any maintenance operations.
Jim.
Sorry John, but Daniel is correct. Anytime you force a connection closed with a JET/ACE Back end, you run the risk of corrupting the database.
It's not a matter of the user just loosing work, but actually having the database messed up and needing a compact and repair.
With that said, killing off a connection in the middle of the night more often then not won't hurt as most users would be idle. But if they have a long running task going, then forcing a disconnect is the last thing you want to do. You are far, far better off to having an inactivity timer and a forced shutdown and lockout built into the app.
And a bigger risk is backup strategy's that simply copy the DB file, which you can do, but absolutely should not. If users are in and writing, you have a very good chance of getting a DB that is inconsistent.
Of course if your using something other than JET/ACE, then that's a different story and you can close a connection without fear. Then it will be just the user that looses work. Still, not a smart thing to do though. Have a multi-step process that's not in a transaction and you could end up with data inconsistencies.
Far better off to have something built into the app to get users out for any maintenance operations.
Jim.
Well...maybe...for a long time i have used this technique for throwing users out as a last resort and if my memory serves me right never had an issue...is not what i would recommend but if everything fails and you just need to work then there isn't another way...
By the way i am talking about FE-BE scenario ...and in my mind unless an operation is running like a transaction i find it a bit difficult how a broken connection could damage a database.
By the way i am talking about FE-BE scenario ...and in my mind unless an operation is running like a transaction i find it a bit difficult how a broken connection could damage a database.
<<and in my mind unless an operation is running like a transaction i find it a bit difficult how a broken connection could damage a database.>>
It's because with JET/ACE, the database engine sits client side.....so if you break the connection to the DB while it's in the middle of it doing something, anything can happen.
There's no server side process to roll things back if a disconnect occurs.
Jim.
It's because with JET/ACE, the database engine sits client side.....so if you break the connection to the DB while it's in the middle of it doing something, anything can happen.
There's no server side process to roll things back if a disconnect occurs.
Jim.
This might be true but because everything boils down to bytes i am thinking that the "logical" thing is to write the bytes directly to the file without extra flags...you just allocate the bytes and populate...if the developers went for a strategy having a flag that marks the End of record and this is committed only when everything is written that might be the case.
Because this a scenario for late night operations i would reckon that we don't have much activity...on the other hand i must confess that the whole talk gave me ideas on how to handle it safely ...too bad i lack an enterprise environment to test it....maybe some other time...or another life.... :)
Because this a scenario for late night operations i would reckon that we don't have much activity...on the other hand i must confess that the whole talk gave me ideas on how to handle it safely ...too bad i lack an enterprise environment to test it....maybe some other time...or another life.... :)
<<This might be true but because everything boils down to bytes i am thinking that the "logical" thing is to write the bytes directly to the file without extra flags>>
You need flags (and locks) because it's multi-user and all users do not run through one DB Engine like it does with SQL Server. So it's impossible to isolate actions by different users. The server is just sharing the file.
The DBH (Database Header Page) contains a two byte flag for each user to indicate what operation is currently in process. Updating the database can mean updating a page where a record is stored, updating a series of LVP's (Long Value Pages) for memo and OLE fields, writing records for attachments in related tables, or updating indexes. So lots of operations can be carried out even for a single record update. If you disconnect a user from the database file during that you end up with a mess.
Jim.
You need flags (and locks) because it's multi-user and all users do not run through one DB Engine like it does with SQL Server. So it's impossible to isolate actions by different users. The server is just sharing the file.
The DBH (Database Header Page) contains a two byte flag for each user to indicate what operation is currently in process. Updating the database can mean updating a page where a record is stored, updating a series of LVP's (Long Value Pages) for memo and OLE fields, writing records for attachments in related tables, or updating indexes. So lots of operations can be carried out even for a single record update. If you disconnect a user from the database file during that you end up with a mess.
Jim.
Probably all these are described on the paper "Understanding Microsoft Jet Locking" ...too bad is gone...i will try to find it...for now i take your word...maybe all these years i was lucky...maybe not
A better reference is the Jet Database Engine Programmers guide, but even that is out of date at this point.
Nothing new on the internals has been written about since 2007 when JET turned into ACE.
Jim.
Nothing new on the internals has been written about since 2007 when JET turned into ACE.
Jim.
Jim.