This book suggests that "Data loss is possible with delayed durability because you might lose committed transactions. If SQL Server crashes after a transaction commits, but before the 60KB buffer is filled, that transaction cannot be recovered because it is lost."

However wont the transactions be available on the transaction log and can be replayed for recovery?



When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.

That's why transactions aren't available in the log, and they will be permanently lost in that scenario.

  • Isnt the delay is to write to the disk? Instead of to the log? – variable Oct 15 '18 at 17:05
  • 2
    @variable the log file IS on disk. That's where it lives. I think you're misunderstanding the difference between the log file and the data pages. SQL Server has never held up transactions to write to the data pages (MDF/NDF files) - those are written asynchronously long after the transaction finishes. – Brent Ozar Oct 15 '18 at 17:13
  • Sorry I meant that: the delay is to write to the data page (disk). I thought it immeidately write to the transaction log as usual. I thought the delay was to write the changes to the data page. Is my understsnding incorrect? – variable Oct 15 '18 at 17:18
  • 5
    @variable Brent's answer is short, but accurate. If you want a more detailed answer, check out the links that Kin posted in the comments on your question. (This sqlperformance.com/2014/04/io-subsystem/… and this sqlskills.com/blogs/paul/delayed-durability-sql-server-2014). The whole point of delayed durability is that it delays writing your transaction to disk AT ALL--not even in the transaction log. Hence the durability of your transaction being delayed. – AMtwo Oct 15 '18 at 17:52
  • @variable no, your understanding is not correct. Delayed Durability means your transaction is considered committed immediately, even before the log file is written to disk. – Brent Ozar Oct 15 '18 at 21:03

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.