Improve Your Performance with SQL Server Transaction Log Using Delayed Durability
What is 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 done correctly. In some scenarios it can cause data loss.
Should we enable it?
Although this can improve performance, the risk of data loss is higher because transaction logs are only saved asynchronously when the logs are flushed to a drive.
For a database in simple recovery mode, there is minimal risk. You have already decided that some data loss is acceptable.
If you are running full recovery, you worry more about data loss. But how much is acceptable? For example, a job that cleans up the log can potentially minimize this risk. The risk will still exist, but it can be minimized for the right workload.
Note: Be careful if you are using an HA form. Data loss can occur even in the event of an uncontrolled shutdown or a planned cluster failover.
How can we enable it?
When using the Alter database statement to enable it at the database level, you have three different options with SET DELAYED_DURABILITY.
Disabled
Default setting similar to full transaction durability.
Allowed
Let each transaction decide the delayed durability.
The durability of all transactions will depend on the transaction level settings.
This option allows database durability transactions to work.
When you allow it, you can specify transaction level durability in the commit statement as in the following example:
COMMIT TRANSACTION TransactionName WITH (DELAYED_DURABILITY = ON)
Forced
Default setting similar to full transaction durability.
You can run the following statement (replace [DatabaseName] in the database) to force your transactions to track in the transaction:
USE [master]
GO
ALTER DATABASE [DatabaseName] SET DELAYED_DURABILITY = FORCED
GOYou can also change these values using SSMS:
- Expand Databases and right-click on one.
- Go to the Options page and change the Delayed Durability option.
