Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ?

Today our log files had reached the limit of the drive and we got a lot of errors. Furthermore, some other services crashed. We increased the disk and restarted the server.

During startup, the server did a "database recovery" -- can I be sure that everything is okay again ?

share|improve this question

migrated from stackoverflow.com Oct 1 '13 at 19:00

This question came from our site for professional and enthusiast programmers.

2  
You can check the error log after recovery is complete to see how many transactions were committed and how many were rolled back. There is no magic answer to this - if a disk crashed then it all depends on the state of the transactions at that time. –  Aaron Bertrand Oct 1 '13 at 18:31
1  
@AaronBertrand But if a transaction is in state "committed" (i.e. commit was successful), how could it then be possible that it gets still lost during the recovery ? –  Giosco Oct 1 '13 at 18:36
3  
@Giosco: It cannot. "committed" means that it is in the database's log file. The only way to lose it after that is to either 1) lose the log file before it is also saved to the data file, or 2) lose both the log file and the data file, or 3) human error. Note that (3) is usually the likeliest. –  RBarryYoung Oct 1 '13 at 18:37
2  
@Giosco how do you know that all of your transactions at the time of the full drive were successfully committed? –  Aaron Bertrand Oct 1 '13 at 18:45
2  
Stored procedure <> transaction - your stored procedure can have multiple explicit transactions, or only implicit, there could be try/catch logic, there could be transaction handling at the C# layer, etc. etc. Again, assuming that no exception = successful commit might be a little too dangerous as a blanket statement. –  Aaron Bertrand Oct 1 '13 at 19:10

1 Answer 1

up vote 16 down vote accepted

If a client application has issued a COMMIT and got back a success code then the transaction is guaranteed by the engine to be durable. It is guaranteed that all changes done in a transaction are going to be visible, even after a crash. Furthermore the recovery also guarantees that any transaction not committed will be rolled back in case of a crash.

For more details I recommend reading the ARIES paper.

Write-ahead logging and recovery cannot make guarantees if the underlying storage hardware has faults (corruption). And any engineering product may contain defects.

Your application, like all other applications, needs to be carefully written to behave correctly in presence of crashes (errors). There is no magic.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.