SQL SERVER – Delayed Durability Database Level and Transaction Level

Earlier I have written three articles on the concept of Delayed Durability. You can read them over here.

After reading an earlier blog post I received a question from a user regarding how database level and transaction level durability settings play with each other when they encounter different settings which are conflicting to each other. MSDN has already provided a very detailed summary of options and their interaction over here.

Though, the grid is very simple, I see lots of people getting confused with the same details. I will attempt to simplify the same grid over here.

  • If the transaction is cross database or distributed, it does not matter what durability settings you have, it is always Fully Durable Transaction.
  • If the transaction is the database level transaction –
    • It is fully durable when the database setting is Disabled for delayed durability
    • It is delayed durable when the database setting is Forced for delayed durability.
    • When the database setting is Allowed – it is delayed durable only if transaction setting is ON as well.

Well, if I have to make my own grid, I will make it as following for easy understanding

Please note that for cross database or distributed transaction’s durability is always set to fully durable, so I have not included in the image metrics.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Exit mobile version