What is the Delayed Durability in SQL Server?
This is a dangerous feature introduced in SQL Server 2014.
Also known as lazy commit, It is a storage optimization that returns a successful commit before transaction logs are saved to a drive.
In some cases, the feature can make a significant performance difference when correctly – in some scenarios, it can cause data loss.
By the way, this check is a part of our SQL Server Health Check service.
Should I enable it?
Although this can improve performance, the risk of data loss is higher because the transaction logs are saved asynchronously, only when the logs are flushed to a drive.
For a database in SIMPLE recovery mode, there is a minimal risk. You have already decided that some data loss is acceptable.
If you are running FULL recovery, so you are more concerned about data loss. But how much is acceptable? A job that flushes the log can potentially minimize that risk, for example. The risk will keep existing, but it can be minimized for the right workload.
Note: Be careful If you are using a form of HA. Even on a graceful shutdown or planned cluster failover, the data loss can happen.
How can I enabled it?
When using the Alter database statement to enable at the Database level, you have three different options with the SET DELAYED_DURABILITY:
Disabled
Default setting, similar to full transaction durability.
Allowed
Allow each transaction to decide the delayed durability.
All the transactions’ durability will be based on the transaction level settings.
This option just enabled the capability of the database to work with transactions that have delayed durability.
When you allowed, you can specify the transaction level durability on the commit statement like the example below:
COMMIT TRANSACTION TransactionName WITH (DELAYED_DURABILITY = ON)
Forced
Forces each transaction to follow this feature.
You can run the statement below to force your transactions to follow delayed durability (replace the [DatabaseName] to your database):
USE [master]
GOALTER DATABASE [DatabaseName] SET DELAYED_DURABILITY = FORCED
GO
You can also change these values using SSMS:
- Expand Databases, and right-click one.
- Go to the options page and change the Delayed Durability option.
More information
Microsoft – Control Transaction Durability.
Reduce SQL Server WRITELOG waits using Delayed Durability, Chandresh Patel – MSSQLTips.
Delayed Durability in SQL Server 2014, Aaron Bertrand – SQLPerformance.
