Using delayed transaction durability

You, as the database administrator, can control Delayed Durability at the database level and can set it using an ALTER database statement as follows:

USE [master]
GO
ALTER DATABASE [DB1] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO

This code changes the database option for Delayed Durability on the DB1 database from Disabled to Allowed.

You can also set this using Management Studio. Right-click on the database whose Delayed Durability settings need to be changed, and choose Properties, and then choose the Options page. You will see a list of options; scroll down until you come to the Delayed Durability option. There you will find a drop-down list with three options: Disabled, Allowed, and Forced. Choose the new value for the option and click on OK as seen in the following screenshot:

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...