SQL Server 2014 – Delayed Transaction Durability

SQL Server 2014 has some great features, with my personal favorite being In-Memory OLTP (Hekaton).  However, in CTP2, a new feature was brought to light which has not received as much attention – Delayed Transaction Durability.  A common misperception is that this feature is only available when using In-Memory OLTP.  In reality, this is just another feature that can be put to use to reduce IO contention.  In this case, the targeted IO contention is transaction log writes.  Additionally, this feature may be helpful when your application is experiencing waits due to locks.  Since delaying log durability results in faster commit times, this effectively results in releasing locks faster.  The downside, is that you may lose data if the log is not flushed to disk for any reason (you can manually execute a flush if needed).

Deploying Delayed Durability

Step 1 – The Database

The database first needs to be configured for delayed durability.  This can be done by turning it on for the entire database, disabling for the entire database, or leaving it configurable at a transaction level.  This is done with an ALTER DATABASE statement.


These settings are fairly self-explanatory, but…

    1. Disabled – this means it can’t be used
    2. Forced – every transaction falls under delayed durability (if it can, exceptions are include things like distributed transactions which will always be durable)
    3. Allowed – this means that each transaction can control whether it is fully durable or delayed durable

Step 2:  The Transaction

If the database allows transaction durability to be configured (delayed_durability = allowed on the database), we have 2 options.  The first is to configure this in a natively compiled stored procedure (In-Memory OLTP), and the second is in a standard TSQL commit.

In-Memory OLTP (the books online example, I will dig into this further in a subsequent blog post)

CREATE PROCEDURE <procedureName> …






LANGUAGE = N’English’



The piece that most people seem to be missing right now is that you can do this now without using a natively compiled stored procedure or in-memory table.  For any transaction (in a database that is marked delayed_durability = allowed), a commit statement can control the durability of the transaction.



There are a few caveats to this.  For example, a distributed transaction will override this setting and proceed with delayed_durability = off.  For more on this, check out books online.


  1. Easy to deploy and use
  2. Use only if you can tolerate some data loss
  3. Use if you are commonly waiting on transaction log writes
  4. Use if you experience heavy contention and locks

My experience working with SQL Server 2014 on IO optimization to this point has been fantastic.  Between In-Memory OLTP and delayed durability, many of the typical IO bottleknecks can be alleviated.  Of course, there are trade-offs, but each project can now be dissected and analyzed as to whether these new features can be used to optimize the workload.  Whether it is a web application, ETL process, etc, I have already pinpointed a number of processes and applications that are prime candidates for these features.  Give them a try, I am sure you will find them useful as well!


One Response to SQL Server 2014 – Delayed Transaction Durability

  1. It would seem like there should be configuration values for how large of a memory buffer to use. Else, in a high-write system I risk hitting a memory barrier with a backlog of writes, and be waiting on disk anyway or the opposite: completely derailing Table cache or something else because too much memory is being used.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: