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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness 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 BrockCIOCommented:
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
Craig YellickDatabase 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)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)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 YellickDatabase 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
Jim Dettman (Microsoft MVP/ EE MVE)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
thank you.. helps to put it in what to expect.
thanks again-
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.