Transaction Log Internals - Pro SQL Server Internals - page 576

Delayed Durability (SQL Server 2014)
Delayed durability , also known as Lazy Commit , is a new feature of SQL Server 2014. As already discussed, by default,
a commit operation is synchronous. SQL Server flushes the content of Log Buffer hardening log records into a log file
at the time of commit, and it sends a confirmation to the client, only after a commit record is written to disk. Delayed
durability changes this behavior making the commit operation asynchronous. The client receives the confirmation
that the transaction is committed immediately without waiting for the commit record to be hardened to disk. The
commit record stays in a log buffer until its content is flushed, which happens in one of the following cases:

The log buffer is full.

A fully durable transaction in the same database is committed. The commit record from
such a transaction flushes the content of the log buffer to disk.

A CHECKPOINT operation occurs.
A sp_flush_log stored procedure is completed successfully.

If SQL Server crashed before the commit record is hardened, the data modifications from that transaction
would be rolled back at recovery as if the transaction had never been committed at all. However, other transactions
would be able to see the data modifications done by such a transaction in between the time of commit and the SQL
Server crash.

■ Data loss is also possible in the case of a regular SQL Server shutdown. even though SQL Server tries to flush
log buffers at the time of shutdown, there is no guarantee that this operation will succeed.

Note

Delayed durability may be a good choice for systems that experience a bottleneck in transaction log writes and
that can tolerate a small data loss. Fortunately, due to the limited size of a log buffer, the possibility of such an event
occurring is relatively small.
One database option, DELAYED_DURABILITY , controls the behavior of delayed durability in the database scope.
It may have one of three options.
DISABLED : This option disables delayed durability for database transactions regardless of
the transaction durability mode. All transactions in the database are always fully durable.
This is the default option and matches behavior of previous versions of SQL Server.
FORCED : This option forces delayed durability for database transactions regardless of the
transaction durability mode.
ALLOWED : Delayed durability is controlled at the transaction level. Transactions are fully
durable unless delayed durability is specified.

It is worth noting that in the case of cross database or distributed transactions; all transactions are fully durable
regardless of their settings. The same applies to Change Tracking and Change Data Capture technologies. Any
transaction that updates tables that are enabled for either of those technologies will be fully durable.
You can control transaction durability by specifying the durability mode in the COMMIT operator. Listing 29-1
shows an example of a transaction that uses delayed durability. As already mentioned, the DELAYED_DURABILITY
database option can override that setting.