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

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
LVL 5
25112Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<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
 
JohnConnect With a Mentor Business Consultant (Owner)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.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
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
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
Craig YellickConnect With a Mentor Database ArchitectCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
 
Craig YellickConnect With a Mentor Database ArchitectCommented:
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
 
25112Author Commented:
thank you.. helps to put it in what to expect.
thanks again-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.