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 ?

  • 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 ? – SQL Police 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

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.

  • If a client application has issued a COMMIT but no response from DB is received until the timeout on client side. What about this commit? success or failure? How to check? – Gab是好人 Feb 1 '17 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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