what happens to MS-Access (Office suite) transactions if sudden disk failure..

25112
25112 used Ask the Experts™
on
if a sudden disk failure happens, how  does MS-Access (Office suite) handle the files in the log (those who were in transaction.. but may not have committed)

I was able to restore the files from a failed disk.. but see that the last days transactions are not there to be seen.. hence the question.

this is windows xp /access 2002
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018
Commented:
if a sudden disk failure happens, how  does MS-Access (Office suite) handle the files in the log (those who were in transaction.. but may not have committed)

In my experience with this kind of issues, transactions are lost.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
If a transaction is not committed, all changes within the transaction are lost, all of them. That's the whole idea behind transactions.

/gustav
Craig YellickDatabase Architect
Commented:
Access is very interesting in this regard. Relational database transactions are described by the acronym ACID for Atomic, Consistent, Isolated and Durable. Access is not a server app, it runs multiple copies at local workstations which work to coordinate data access including transactions. This is accomplished by creating, monitoring and updating an additional file with a .laccdb extension that you see in the same folder as the main database file. This file functions as a lock manager as well as a transaction log coordinator.

The A, C and I can be accomplished through careful coordination between the multiple instances of Access. D= Durability is difficult when the instances "go away". There's nobody there to rollback uncommitted changes. So the database just sits there in an indeterminate state, until a copy of Access attempts to open the database. It notices the uncommitted transaction and displays an error message, and offers to perform the actions that should have been done previously.

So... to answer your question: it depends on exactly how the disk failed. So long as the .laccdb is intact, Access can figure out what needs to be rolled back. So long as the .accdb is intact, it can be rolled back. If the files are corrupted it could be a total loss.

-- Craig
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<So... to answer your question: it depends on exactly how the disk failed. So long as the .laccdb is intact, Access can figure out what needs to be rolled back. So long as the .accdb is intact, it can be rolled back. If the files are corrupted it could be a total loss.>>

  No, the .laccdb file doesn't track transactions.   JET/ACE uses temp files for logging transactions if it has to.  But even so, there's no roll-forward process available and in some cases, no roll-back either.  

 There's an old thread here where we discussed transactions in detail and what it boils down to is if an abnormal disconnect occur, you may get a partial commit.   If a disk fails, you can't roll forward and may have to resort to a backup to get a db that's in a consistent state.

 Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
I've used Transactions in VBA for years, and just finished another project as well.  Fortunately, I've never encountered a 'partial commit'.  But I'm confident that if there is a failure *during* the Commit ... the the result is indeterminate.
I was asking myself have I been missing something all these years regarding is the Lock File holding transaction information?  
I've learned a lot of little nuances about using Transactions over the years, and I have found them to be quite reliable and amazing as well.
Craig YellickDatabase Architect
Commented:
The .laccdb file knows about the locks required for transactions, and will resolve them if there is no commit. The temp files contain the uncommitted changes so they are not needed for the rollback. In that sense the term "rollback" isn't really accurate but that's how I think of it.

I could be way off on this. It's been years since I experimented with these concepts. Back in the day I had a tool that could read the contents of .ldb files (the older version file extension for .laccdb) and it was fascinating to see what Access did behind the scenes. My recollection is that transactions involve a lot of locks.

-- Craig
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<I was asking myself have I been missing something all these years regarding is the Lock File holding transaction information?  >>

 No, nothing new.  The LDB file will never exceed 16k in size.  It holds a 255x64 byte array; 32 bytes for the machine name and 32 bytes for the user (Access user that is) for each user that is logged into the DB.   This is a one for one match with the database header page when you open a DB.

 Beyond that, the only other thing the LDB is used for is to place locks against it, which the OS does on behalf of MSACCESS.EXE.    The locks are virtualized in the OS (they are never physically written to disk).   The DB file itself never has a lock placed against it.

 Jim.

Author

Commented:
thank you.. helps to put it in what to expect.
thanks again-

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial