Delayed Durability

Available in SQL Server 2014 – All Editions

Delayed Durabilty is a way to defer the acknowledgement of the transaction log records getting written to disk, i.e. transactions can continue without waiting, and SQL will assume that the log record *will* be written. You may know the ACID rules (Atomicity, Consistency, Isolation, Durability) that guarantee that when a transaction is committed it stays committed. Allowing delayed durability basically removes the D. Normally Durability is achieved through write-ahead logging (WAL) which means that transactions are written to disk before the transaction is complete. With delayed durability enabled you are trading durability for performance. So, be aware that you can lose data

SQL uses 60KB of the log buffer, and will attempt to flush the log to disk when the 60KB block is full. There is a risk of data loss with this. Delayed Durability option is set at the database level, at the individual transaction level, or (for natively compiled procedures in In-Memory OLTP) at the procedure level. It is best to enable if your transaction log is a bottleneck and you are experiencing high WRITELOG waits.

If you need to you can force the transaction log to flush delayed transactions by executing sys.sp_flush_log.

Database

ALTER DATABASE <dbname> SET DELAYED_DURABILITY = DISABLED | ALLOWED | FORCED

• ALLOWED = individual transactions can use Delayed Durability.
• FORCED = all transactions that can use Delayed Durability will
• DISABLED = committing a transaction using the delayed option will be ignored with no error message

Transaction

Add to the transaction commit….

COMMIT TRANSACTION TransactionName WITH (DELAYED_DURABILITY = ON)

In-Memory OLTP (EE only)

Add to the BEGIN ATOMIC block…

CREATE PROCEDURE [dbo].[NewRoadRace] @RaceID INT, @RaceName VARCHAR(500), @LengthMiles INT, @State CHAR(2)

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC WITH (DELAYED_DURABILITY = ON, TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ‘us_english’)

–Insert

INSERT INTO dbo.RoadRaces (RaceID, RaceName, LengthMiles, [State]) VALUES (@RaceID, @RaceName, @LengthMiles, @State)

END

GO