SQL Server Delayed Durability: What It Is, When to Use It, and When to Leave It Alone

Open SQL Server Management Studio, right-click any database, go to Properties, and click Options. Near the bottom of the list you will find a setting called Delayed Durability with three values: Disabled, Allowed, and Forced. Most DBAs have seen it. Most have left it at Disabled and moved on. A few have switched it to Forced after reading that it improves performance and later wondered why they lost data after a server crash.

Delayed Durability is a real performance tool with a real trade-off. Understanding exactly what it does, what it costs, and which workloads justify that cost is what this article covers. By the end you will know what the setting does at the log buffer level, when WRITELOG waits are the right signal to consider it, which workloads are good candidates, which workloads should never use it, how to enable it at each of its three scopes, and how to use sp_flush_log to limit your exposure if you do enable it.

1 What Full Durability Means and Why It Costs I/O Beginner

SQL Server uses Write-Ahead Logging (WAL) to guarantee that committed transactions survive crashes. The rule is simple: before a transaction commit is acknowledged to the client, the log records for that transaction must be written to disk. Not cached. Not queued. Written to disk and confirmed. Only then does SQL Server tell the application that the commit succeeded.

This guarantee is what makes the D in ACID: Durability. When your application receives a commit confirmation, it can trust that the data is safe regardless of what happens next to the server, power, or operating system. The transaction is on disk.

The cost is I/O latency. Every commit requires a synchronous disk write to the transaction log file. On a busy OLTP system processing thousands of small transactions per second, the transaction log becomes a write bottleneck. Each commit waits for the log write to complete before the next commit can proceed. On spinning disk this wait is measurable in milliseconds. On SSD it is shorter but still present. On any storage type, a sufficiently high commit rate can cause the log write to become the limiting factor on throughput.

2 What Delayed Durability Changes Beginner

Delayed Durability changes when the commit is acknowledged to the client. Instead of waiting for the log to be flushed to disk, SQL Server places the log records in the log buffer in memory and immediately returns a commit confirmation. The actual disk write happens later, asynchronously.

The log buffer in SQL Server holds up to 60KB of log records. When the buffer fills to 60KB, SQL Server flushes it to disk automatically. It also flushes on certain other conditions including a fully durable transaction committing in the same database, a checkpoint, or an explicit call to sys.sp_flush_log. Between those flush points, committed transactions exist only in the log buffer in memory.

The performance benefit is real and measurable. On workloads bottlenecked by log write latency, removing the synchronous wait can dramatically increase transaction throughput. The cost is equally real: any transactions acknowledged but not yet flushed to disk are lost if the server crashes, loses power, or is forcibly shut down before the flush occurs.

3 The ACID Trade-off: Removing the D Beginner

The four ACID properties are Atomicity, Consistency, Isolation, and Durability. Delayed Durability removes the guarantee of Durability for affected transactions. The other three properties are preserved: the transaction is still atomic, still consistent, still isolated. It just is not guaranteed to survive a crash after the commit is acknowledged.

4 Why It Is in SQL Server Options Beginner

Delayed Durability was introduced in SQL Server 2014. Microsoft added it specifically for workloads where the ACID durability guarantee is not required for every transaction and where log write latency is the measurable bottleneck on performance.

The feature exists because not all data has equal criticality. A financial transaction must be durable. A session logging table that records every page view to a high-traffic web application does not carry the same business risk if a few seconds of log entries are lost in a crash. A telemetry pipeline collecting sensor readings from IoT devices can tolerate a small gap. A staging table that gets truncated and reloaded nightly has no durability requirement at all because the authoritative source is elsewhere.

Microsoft exposed it at the database level in the Options panel so DBAs can make a deliberate, informed decision about which databases or workloads operate with this trade-off. The default is Disabled, which means no database uses it unless a DBA explicitly enables it. The SSMS Options placement is intentional: it is a database-level architectural decision, not a query hint or session setting.

It also integrates with In-Memory OLTP (Hekaton). Memory-optimized tables with Delayed Durability can achieve extremely high throughput because they bypass both the standard log write path and the buffer pool entirely for many operations. For In-Memory OLTP workloads where throughput is the primary requirement and durability is explicitly not required, Delayed Durability is part of the performance architecture.

5 The Signal That Makes It Worth Considering Intermediate

The first and most important thing to verify before considering Delayed Durability is whether your workload is actually bottlenecked by log writes. Enabling it on a database that is not log-write bound will buy you nothing on performance and will expose you to data loss for no reason.

The wait type to look for is WRITELOG. This wait accumulates when SQL Server is waiting for the log buffer to be flushed to disk during a commit. High WRITELOG waits are the primary indicator that log write latency is limiting transaction throughput.

-- Check current wait statistics for log-write bottlenecks
-- WRITELOG is the key signal for Delayed Durability candidacy

SELECT TOP 20
    wait_type,
    wait_time_ms / 1000.0               AS WaitSeconds,
    waiting_tasks_count,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS AvgWaitMs,
    ROUND(100.0 * wait_time_ms
        / NULLIF(SUM(wait_time_ms) OVER (), 0), 2) AS PctOfTotal
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP',
    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','HADR_WORK_QUEUE',
    'KSOURCE_WAKEUP','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
    'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
    'RESOURCE_QUEUE','SERVER_IDLE_CHECK','SLEEP_DBSTARTUP',
    'SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
    'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK',
    'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SQLTRACE_BUFFER_FLUSH','WAITFOR','XE_DISPATCHER_JOIN',
    'XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- WRITELOG in the top 5 waits = investigate Delayed Durability
-- WRITELOG not in top 10 = log writes are not your bottleneck,
-- do not use Delayed Durability

-- Measure actual log write latency per database
-- High avg_write_stall_ms on the log file confirms the bottleneck

SELECT
    DB_NAME(vfs.database_id)            AS DatabaseName,
    mf.physical_name,
    mf.type_desc,
    vfs.io_stall_write_ms,
    vfs.num_of_writes,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms / vfs.num_of_writes
         ELSE 0
    END                                 AS AvgWriteStallMs,
    vfs.io_stall_read_ms,
    vfs.num_of_reads,
    CASE WHEN vfs.num_of_reads > 0
         THEN vfs.io_stall_read_ms / vfs.num_of_reads
         ELSE 0
    END                                 AS AvgReadStallMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON  mf.database_id = vfs.database_id
    AND mf.file_id     = vfs.file_id
WHERE mf.type_desc = 'LOG'  -- log files only
ORDER BY AvgWriteStallMs DESC;

-- Thresholds:
-- AvgWriteStallMs > 10ms = worth investigating
-- AvgWriteStallMs > 25ms = likely bottleneck
-- AvgWriteStallMs > 50ms = significant log write problem

-- Also check VLF count -- excessive VLFs can contribute to WRITELOG waits
-- before going to Delayed Durability, fix VLF count first
DBCC LOGINFO;  -- count the rows. Over 1000 is problematic

6 Workloads That Are Good Candidates Intermediate

Delayed Durability is appropriate when two conditions are both true: the workload is actually bottlenecked by log write latency, and the business can accept losing a small window of committed transactions in a crash scenario. Both conditions must be true. Either one alone is not sufficient justification.

Logging and Audit Tables

Application event logs, user activity logs, audit trails, clickstream data. These tables receive high insert volumes and losing a few seconds of entries in a crash is acceptable because the entries are informational rather than transactional. The application continues correctly even if a small gap exists in the log data.

Telemetry and Sensor Data Pipelines

IoT sensor readings, performance metrics, monitoring data arriving at high frequency. If the authoritative source of the data is the sensor or monitoring agent, losing the most recent buffer of readings in a crash is acceptable because those readings still exist at the source and can be replayed or are simply not worth re-collecting for historical purposes.

Staging and ETL Landing Tables

Tables that receive data as part of an ETL pipeline where the source system retains the data and the pipeline is idempotent. If a crash occurs during a load, the pipeline reruns from the last successful checkpoint. Losing a few seconds of staged rows is acceptable because they can be reloaded.

Session State and Cache Tables

SQL-backed session state where a session being reset to empty after a crash is a tolerable user experience rather than a data integrity violation. The user re-authenticates and starts a new session.

High-Throughput In-Memory OLTP

Memory-optimized tables with schema-only or delayed-durability configuration where maximum throughput is explicitly the design goal. The combination of In-Memory OLTP and Delayed Durability is one of the highest-throughput configurations SQL Server supports. Use this explicitly designed combination for queuing, caching, and high-frequency write workloads where recovery from the source is part of the design.

7 Workloads That Should Never Use It Beginner

The list of workloads that should not use Delayed Durability is longer and more important than the list of candidates. If any of these apply, leave Delayed Durability Disabled.

• Financial transactions. Any table recording money movement, account balances, orders, payments, or invoices requires full durability. A committed transaction that disappears after a crash is not just a data quality issue. It is a regulatory and legal problem. The word “committed” has a specific meaning in financial systems and Delayed Durability changes what it means.
• Tables with foreign key relationships to other databases. If a transaction commits in a delayed-durable database and that commit represents data that other databases reference, a crash can create referential integrity violations that are impossible to detect or resolve automatically.
• Any table where the committed data is the authoritative record. If there is no other copy of the data and the database is the system of record, the committed data must survive a crash.
• Compliance and regulated data. HIPAA, PCI-DSS, SOX, and similar regulatory frameworks have data integrity requirements that conflict with voluntary data loss. Enabling Delayed Durability on regulated data is a compliance risk.
• Tables involved in distributed transactions. Delayed Durability interacts poorly with cross-database distributed transactions. The committed state of a delayed-durable transaction in one database is not synchronized with other databases in the transaction, which can create inconsistencies after a crash.
• Any database using Database Mirroring. Database Mirroring does not support Delayed Durability. The log records must be flushed before they can be sent to the mirror. Enabling Delayed Durability on a mirrored database will force full durability anyway, providing no benefit while adding confusion.

8 The Three Scopes: Database, Transaction, and Procedure Intermediate

Delayed Durability can be controlled at three levels. The database-level setting is the gate that determines whether delayed durability is possible at all. The transaction-level and procedure-level settings determine whether a specific transaction actually uses it.

The ALLOWED setting is the most surgical and the safest approach. It leaves full durability as the default and allows specific transactions to opt in where it is appropriate. This way a database with mixed workloads (a logging table and a financial transaction table) can have delayed durability only on the logging inserts while the financial transactions remain fully durable.

9 How to Enable and Disable It Intermediate

-- ============================================================
-- CHECK CURRENT SETTING ON ALL DATABASES
-- ============================================================

SELECT
    name                                AS DatabaseName,
    delayed_durability,
    delayed_durability_desc             AS DelayedDurabilityDesc
FROM sys.databases
WHERE database_id > 4   -- user databases only
ORDER BY name;

-- delayed_durability values:
-- 0 = DISABLED (default, fully durable)
-- 1 = ALLOWED  (opt-in at transaction level)
-- 2 = FORCED   (all transactions delayed durable)

-- ============================================================
-- SET AT DATABASE LEVEL
-- ============================================================

-- ALLOWED: opt-in per transaction (safest approach)
ALTER DATABASE [YourDatabase]
SET DELAYED_DURABILITY = ALLOWED;

-- FORCED: all transactions delayed durable (use only on appropriate workloads)
ALTER DATABASE [YourDatabase]
SET DELAYED_DURABILITY = FORCED;

-- DISABLED: back to full durability (default)
ALTER DATABASE [YourDatabase]
SET DELAYED_DURABILITY = DISABLED;

-- ============================================================
-- OPT IN AT THE TRANSACTION LEVEL
-- (requires database to be set to ALLOWED)
-- ============================================================

BEGIN TRANSACTION;

    INSERT INTO dbo.EventLog (EventType, EventTime, Message)
    VALUES ('UserLogin', SYSDATETIME(), 'User authenticated');

COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);
-- This commit does not wait for log flush
-- Only appropriate when database is ALLOWED

-- For comparison: force full durability on a specific transaction
-- even when database is set to ALLOWED
BEGIN TRANSACTION;

    INSERT INTO dbo.FinancialTransactions (Amount, AccountID, TransactionDate)
    VALUES (1500.00, 10042, SYSDATETIME());

COMMIT TRANSACTION WITH (DELAYED_DURABILITY = OFF);
-- This commit always waits for log flush regardless of database setting
-- Use this to protect critical transactions in ALLOWED databases

-- ============================================================
-- NATIVELY COMPILED STORED PROCEDURE (In-Memory OLTP)
-- ============================================================

CREATE PROCEDURE dbo.usp_InsertTelemetry
    @SensorID   INT,
    @Reading    DECIMAL(10,4),
    @ReadingTime DATETIME2
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (
    DELAYED_DURABILITY = ON,        -- delayed durable commit
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    INSERT INTO dbo.SensorReadings (SensorID, Reading, ReadingTime)
    VALUES (@SensorID, @Reading, @ReadingTime);
END;

10 Using sp_flush_log to Limit Data Loss Exposure Intermediate

If you enable Delayed Durability but want to limit how much data could be lost in a crash, sys.sp_flush_log is the mechanism Microsoft provides. Calling it forces an immediate flush of all pending log records to disk, hardening all previously committed delayed-durable transactions. After a successful sp_flush_log call, those transactions are fully durable even though they were committed with delayed durability.

-- Manual flush: harden all pending delayed-durable transactions now
EXEC sys.sp_flush_log;
-- Returns 1 on success

-- Automated flush via SQL Agent job
-- Run this job every N seconds to limit maximum data loss exposure
-- If you cannot afford to lose more than 10 seconds of data:
-- create a job with a 10-second schedule that executes sp_flush_log

-- Example: create a scheduled flush job
USE msdb;
GO

EXEC sp_add_job
    @job_name = 'SQLYARD - Flush Delayed Durability Log';

EXEC sp_add_jobstep
    @job_name = 'SQLYARD - Flush Delayed Durability Log',
    @step_name = 'Flush log',
    @command = 'EXEC sys.sp_flush_log;',
    @database_name = 'YourDatabase';

EXEC sp_add_schedule
    @schedule_name = 'Every 30 seconds',
    @freq_type = 4,              -- daily
    @freq_interval = 1,
    @freq_subday_type = 2,       -- seconds
    @freq_subday_interval = 30;  -- every 30 seconds

EXEC sp_attach_schedule
    @job_name = 'SQLYARD - Flush Delayed Durability Log',
    @schedule_name = 'Every 30 seconds';

EXEC sp_add_jobserver
    @job_name = 'SQLYARD - Flush Delayed Durability Log';
GO

11 Delayed Durability and Always On Availability Groups Intermediate

Delayed Durability has important interactions with Always On Availability Groups that require understanding before enabling it in an AG environment.

In a synchronous-commit AG, the primary cannot acknowledge a transaction commit until the secondary has hardened the log records. Delayed Durability at the database level affects this behavior. When Delayed Durability is FORCED, the primary can acknowledge commits without waiting for the secondary because the commit itself does not wait for local disk hardening. This means a delayed-durable transaction on a synchronous AG primary is not guaranteed to have reached the secondary before the commit is acknowledged.

SQL Server 2016 improved the Transaction Log synchronization mechanism for Delayed Durability and implemented algorithms to process log entries with minimal loss in AG scenarios. However the risk of data loss is not completely eliminated even in SQL Server 2016 and later. If the primary crashes after acknowledging delayed-durable commits but before the log records reach the secondary, those transactions may be lost even in a synchronous AG configuration.

12 Checking Whether It Is Enabled on Your Servers Beginner

If you are inheriting a SQL Server environment or conducting a health check, this query tells you immediately whether any databases have Delayed Durability enabled and at what level.

-- Health check: find all databases with Delayed Durability enabled
-- Run this on every SQL Server instance you manage

SELECT
    d.name                              AS DatabaseName,
    d.delayed_durability_desc           AS DelayedDurabilityState,
    d.state_desc                        AS DatabaseState,
    d.recovery_model_desc               AS RecoveryModel,
    d.create_date,
    -- Flag databases where this warrants review
    CASE d.delayed_durability
        WHEN 0 THEN 'Standard: fully durable'
        WHEN 1 THEN 'REVIEW: opt-in delayed durability enabled'
        WHEN 2 THEN 'REVIEW: all transactions delayed durable - verify this is intentional'
    END                                 AS ReviewNote
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY d.delayed_durability DESC, d.name;

-- If any rows show delayed_durability = 1 or 2:
-- 1. Verify it was intentionally enabled by a DBA who understood the trade-off
-- 2. Confirm the databases and workloads are appropriate candidates
-- 3. Confirm sp_flush_log is running on a schedule if data loss exposure needs to be bounded
-- 4. Confirm the teams responsible for those databases understand the crash behavior

References

• Microsoft Docs: Control Transaction Durability (SQL Server 2025)
• Microsoft Docs: sys.sp_flush_log
• Microsoft Docs: Defining Durability for Memory-Optimized Objects
• SQLServerCentral: What Is Delayed Durability in SQL Server and Should You Turn It On? (August 2025)
• Glenn Berry (SQLskills): Delayed Durability in SQL Server
• SQLShack: Improve SQL Server Transaction Log Performance with Delayed Durability
• AWS Prescriptive Guidance: Enable Delayed Durability for SQL Server on EC2
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server Fragmentation Complete Guide
• SQLYARD: Always On Availability Groups Complete Guide