SQL SERVER – Delayed Durability Database Level and Transaction Level

How do database level and transaction level durability settings work together? 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?
• Example of Delayed Durability: How Much Faster Are Commits?

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.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.