Solved

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

Posted on 2016-08-08
8
111 Views
Last Modified: 2016-08-08
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
0
Comment
Question by:25112
8 Comments
 
LVL 90

Assisted Solution

by:John Hurst
John Hurst earned 62 total points
Comment Utility
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.
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 62 total points
Comment Utility
If a transaction is not committed, all changes within the transaction are lost, all of them. That's the whole idea behind transactions.

/gustav
0
 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 62 total points
Comment Utility
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
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 252 total points
Comment Utility
<<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.
2
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 62 total points
Comment Utility
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.
0
 
LVL 11

Assisted Solution

by:CraigYellick
CraigYellick earned 62 total points
Comment Utility
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
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 252 total points
Comment Utility
<<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.
1
 
LVL 5

Author Comment

by:25112
Comment Utility
thank you.. helps to put it in what to expect.
thanks again-
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now