What’s the issue?
Delayed durability is a database option introduced in SQL Server 2014 that allows transactions to commit before their log records are actually written to disk. Normally, SQL Server follows strict write ahead logging, meaning a transaction is not considered committed until its log records are hardened to the transaction log file.With delayed durability enabled, log records are buffered in memory and written asynchronously, allowing the committing transaction to return control to the application immediately. The setting can be configured at the database level as DISABLED, ALLOWED, or FORCED.
Why is this a problem?
Delayed durability trades data safety for performance. If SQL Server crashes, the host loses power, or the service stops unexpectedly before buffered log records are flushed, any transactions that committed with delayed durability but had not yet been written to disk will be lost, even though the application was told the commit succeeded. This breaks the durability guarantee that most applications assume, and can result in silent data loss that is not detectable until someone notices missing records. When set to FORCED, every transaction in the database is affected regardless of how it was written, meaning even well designed application code cannot opt out.What should you do about this?
Identify affected databases by querying sys.databases and checking the delayed_durability_desc column for any value other than DISABLED. Review with application owners and development teams whether delayed durability was enabled intentionally for a specific high throughput, loss tolerant workload, or whether it was turned on without full understanding of the implications. In most cases it should be disabled using ALTER DATABASE [DatabaseName] SET DELAYED_DURABILITY = DISABLED;.If there is a legitimate performance reason to use it, prefer the ALLOWED setting over FORCED so individual transactions can opt in explicitly, and ensure the business has formally accepted the risk of potential data loss in writing.