SQL SERVER – Delayed Durability and Flushing Log Files

Flushing with sys.sp_flush_log hardens preceding committed delayed-durable transactions in the current database. I use delayed durability only with an accepted tradeoff.

SELECT name, delayed_durability_desc FROM sys.databases WHERE name=DB_NAME();
-- Approved flush in the intended database, SQL Server 2014+:
-- EXEC sys.sp_flush_log;

A delayed-durable commit can acknowledge success before log records harden. A crash or failover before flushing can lose acknowledged work. This procedure concerns the transaction log. It is not a data-page flush or a backup.

My old SQL statement accidentally contained HTML code markup. The corrected command is an administrative template. Use the intended database and verify success before treating preceding committed work as durable.

Periodic flushing reduces exposure when it executes successfully. Delays or failures prevent a guaranteed wall-clock loss limit. Fully durable commits provide another documented flush boundary. Choose according to recovery requirements and measured workload.

Reference: Delayed transaction durability and flush boundaries.

Related reading

• Comprehensive Database Performance Health Check
• LinkedIn

A periodic flush is not a guaranteed loss-time bound, it is a durability boundary whose execution must succeed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.