Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Delayed Durability is a feature that introduced with SQL Server 2014. It is a feature that flexs Durability, a common feature of advanced relational databases. You may want to read the article “SQL Server ACID Rules“.
By default, SQL Server works as follows:
Unless the relevant record in the log cache is physically written to the transaction log file(this is known as log flush), the record that has changed in the buffer cache is not written physically to the data file. This protect mechanism called as Write Ahead Logging. You can find the details in the article “What is Database Checkpoint”
Therefore, if the data is not written to the ldf file, the completion information is not transmitted to the user. Thus, in case of any crash, sql server can recover the data from the log file. This ensures that your data will not be lost.
With Delayed Durability, we can change this behaviour at the database level, at the commit process level or at the atomic block level. This means that the SQL Server informed the user that the process is completed before writing data to the log file.
NOTE: If you enable delayed durability at the database, transaction, or atomic block level, your performance will increase, but there is a risk of data loss.
For example, let’s assume that you enabled delayed durability and therefore SQL Server informed the user that the process is completed successfully before writing data to the log file. But in fact, there will be no relevant record in the transaction log due to delayed durability. Suppose that there was an unexpexted server crash at this moment. This will cause a problem. The user thinks that the data has been inserted to the database. But it wasn’t actually inserted.
If you do not understand this part, please read the article “What is Database Checkpoint“.
With the help of the script below, we are able to set delayed durability options at database level.
1 | ALTER DATABASE Your_Db_Name SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED } |
As you can see, DISABLED, ALLOWED and FORCED options are available.
DISABLED is the default behavior. This means that if you have previously set Delayed Durability at the database level, you can revert back to the default behavior with the following script.
1 | ALTER DATABASE Your_Db_Name SET DELAYED_DURABILITY=DISABLED |
ALLOWED means that durability is allowed to be specified in transactions within the relevant database.
If you set ALLOWED, you can set transaction delayed durable as follows at transaction level.
1 2 3 | BEGIN TRANSACTION select 1; COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON) |
In Natively Compiled Stored Procedures, you can set delayed durability at atomic block level as follows. For detailed information about Natively Compiled Stored Procedure, you should read “What is Natively Compiled Stored Procedure in SQL Server“.
1 2 3 4 5 6 7 8 9 10 11 | CREATE PROCEDURE NativelyCompiledSP_Example WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING AS BEGIN ATOMIC WITH ( DELAYED_DURABILITY = ON, TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English' … ) END --EXEC NativelyCompiledSP_Example |
If set to FORCED, all transactions in the database are forced to run as delayed durable.
1 | ALTER DATABASE YourDBName SET DELAYED_DURABILITY=FORCED |
