SQL SERVER – How to Setup Delayed Durability for SQL Server 2014?

9 years ago

Yesterday we discussed about the Basics of Delayed Durability in SQL Server 2014.

There are three methods to setup the this feature in SQL Server. Let us see each of them in detail.

Method 1: Database Level

You can enable, disable or force delayed durability at database level. Here is how you can do the same.

USE [master]
GO
-- Enable Delayed Durability for the database
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = ALLOWED
GO

If you want your changes immediately take action, you can additionally use an option WITH NO_WAIT which will be implemented immediately.

USE [master]
GO
-- Enable Delayed Durability for the database
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = ALLOWED
GO

Currently there are three different options with the SET DELAYED_DURABILITY.

Disabled: This is the default setting and very similar to full transaction durability.
Allowed: This option will allow each transaction to decide the delayed durability. Once this enables each transactions’s durability will be based on the transaction level level settings which will see later in this post.
Forced: This option will force each transaction to follow this feature.

There is one more thing we need to understand before we continue further down. When we set SET DELAYED_DURABILITY = ALLOWED it does not mean that each of the transactions are going to follow this durability. Allowed is just simply enabling the capability of the database to work with transactions which will have delayed durability. If you want each of your transactions to follow delayed durability you will have to execute the following statement.

USE [master]
GO
-- Enable Delayed Durability for the database
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = FORCED
GO

You can disable delayed durability by executing the following statement.

USE [master]
GO
-- Enable Delayed Durability for the database
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = DISABLED
GO

You can also change these values from SSMS as displayed in the image below.

Method 2: Transaction Level

Now that we have enabled the database level transactions, we can now use transaction level settings for delayed durability. Remember, if you have not enabled database level transaction, specifying transaction level durability will have no impact. You can specify the transaction level durability on the commit statement as following.

COMMIT TRANSACTION nameoftransaction WITH (DELAYED_DURABILITY = ON);

Method 3: Natively Compiled Stored Procedure

You can use the similar settings for natively compiled stored procedures as well. Here is the example of the syntax.

CREATE PROCEDURE <procedureName> …
 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
 AS BEGIN ATOMIC WITH
 (
 DELAYED_DURABILITY = ON,
 ...
 )
 END

Well, that’s it for today.

Reference: Pinal Dave (https://blog.sqlauthority.com)