SQL Server Delayed Durability: Faster Commits, Real Data Loss Risk

When a transaction commits in SQL Server, the client only gets the “done” response after the transaction log record has been written to disk. That wait guarantees the data survives even if the server crashes; but if the log disk is slow or the application sends lots of small transactions, every commit waiting for that write becomes the bottleneck, and WRITELOG waits go through the roof.

Delayed durability, introduced in SQL Server 2014, is an option that skips that wait: the commit returns successfully before the log is written to disk. The gain can be real, and so is the price: if the server crashes, the most recently committed transactions can be lost. This article explains how it works, when it’s acceptable, and the alternatives to try first.

How It Works

When a delayed durable transaction commits, its log records stay in the in-memory log buffer first. The buffer is written to disk when:

• The log buffer fills up.
• A fully durable transaction commits in the same database.
• sys.sp_flush_log is called.
• SQL Server shuts down cleanly.

If the server goes down unexpectedly in the meantime (a power failure, an operating system crash, the process being killed), the log records in the buffer are lost, and transactions the application believes were committed don’t come back.

How to Enable It

The database-level setting takes three values:

-- DISABLED: the default, every commit is fully durable
-- ALLOWED : each transaction can choose
-- FORCED  : every commit is delayed durable
ALTER DATABASE StagingDB SET DELAYED_DURABILITY = ALLOWED;

-- Choosing at transaction level when ALLOWED
BEGIN TRAN;
    INSERT INTO dbo.LoadLog (Source, RowCnt) VALUES (N'ERP', 1500);
COMMIT TRAN WITH (DELAYED_DURABILITY = ON);

-- Flush pending log records to disk at a critical point
EXEC sys.sp_flush_log;

To check the current state:

SELECT name, delayed_durability_desc
FROM sys.databases
WHERE delayed_durability_desc <> N'DISABLED';

When Is It Acceptable?

• Reproducible data: staging and ETL work tables that can be reloaded from the source.
• Records where loss is tolerable: detailed application logs, telemetry or click data.
• Bulk loads: load jobs that can simply be rerun if interrupted.

Limits: delayed durability doesn’t apply to cross-database or distributed transactions; those are always fully durable. In Always On, if the primary goes down unexpectedly, delayed durable transactions not yet sent to the secondary can be lost.

Try These First

Before switching to delayed durability, find the cause of the log write bottleneck:

• Log disk latency: log write latency above a few milliseconds goes straight into commit time. Moving the log file to a fast, separate disk often fixes the problem at the root.
• Very small transactions: inserting row by row in a loop with a commit per row means thousands of separate log writes. Grouping the work into reasonably sized batches with one commit each gives the same gain with no risk of data loss.
• Unnecessary log volume: updating unchanged columns in large updates, or unnecessary indexes, increase log volume.

-- Log write latency (ms) and WRITELOG waits
SELECT DB_NAME(vfs.database_id) AS database_name,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
WHERE mf.type_desc = N'LOG'
ORDER BY avg_write_ms DESC;

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = N'WRITELOG';

Checklist

• WRITELOG waits and log disk latency have been measured.
• The log file is on a fast, separate disk.
• Small transactions are grouped wherever possible.
• Delayed durability is used only for data where loss is acceptable, with the business’s approval.
• FORCED mode isn’t used on critical databases.