Earlier I have written three articles on the concept of Delayed Durability. You can read them over here.
- SQL SERVER – Basics of Delayed Durability in SQL Server 2014
- SQL SERVER – How to Setup Delayed Durability for SQL Server 2014?
- SQL SERVER – Simple Example of Delayed Durability
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)
