| Transactions with Delayed Durability in SQL Server used to reduce latency. And will return control to the client before the Transaction log record is written to disk. We can define this at database level, Commit level or Atomic block level in compiled stored procedure. And sys.sp_flush_log Stored Procedure added to flush the Transaction Log to disk to make previously committed transactions durable with delayed durability.
// Set allow transactions with delayed durability
USE [master]
GO
ALTER DATABASE [DBName] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO
BEGIN TRANSACTION
UPDATE dbo.TableName
SET ColumnName = @DataValue
WHERE condition if any
// Now code to set current transaction with delayed durability
COMMIT TRANSACTION WITH(DELAYED_DURABILITY = ON )
// Now do transaction log to disk
EXEC sys.sp_flush_log | | |