Delayed Durability - Getting Started with SQL Server 2014 Administration - page 48

There are three options available to the DBA when setting the option for Delayed
Durability:
• Disabled : This is the default setting. All transactions will be fully durable
in SQL Server 2014 out of the box, unless you, as the DBA, choose the
contrary. All transactions are fully durable regardless of any commit-level
settings at the transaction level. This is the default setting and will prevent
any unwanted data loss as a result of Delayed Durability. Yes, you read that
correctly; it is possible to lose data using Delayed Durability. You would use
Delayed Durability only when performance is more important than data loss.
• Allowed : If this option is selected, then the durability can be set at the
transaction level. We will look at the delayed_durability = on or off
statement in the next section.
• Forced : If you set the Delayed Durability setting to be forced at the database
level, then all transactions, regardless of any transaction level Delayed
Durability settings, will use Delayed Durability. This setting can be used
when Delayed Durability will benefit the database as a whole. There will be
no need to modify existing database or application code as long as you are
happy to accept the potential data loss that could result in the event of
a system restart or crash.
Transaction level Delayed Durability -
Transact-SQL
If the database level Delayed Durability setting is set to Allowed , it means you can
control Delayed Durability at the transaction level. The Transact-SQL syntax looks
like this:
DELAYED_DURABILITY = { OFF | ON }
At the transaction level, the DELAYED_DURABILITY is either ON or OFF .
If the setting is set to off, which is the default setting, then the transaction will be
fully durable. The only exception to this would be if at the database level the Delayed
Durability has been set to Forced . In which case, the transaction will use Delayed
Durability regardless of the transaction level setting.
If at the transaction level we set Delayed Durability to be on, the transaction will
be delayed durable. The only exception to this would be if at the database level the
Delayed Durability option has been set to disabled in which case all transactions
would be fully durable regardless of the setting at the transaction level.