Available in SQL Server 2014 – All Editions
Delayed Durabilty is a way to defer the acknowledgement of the transaction log records getting written to disk, i.e. transactions can continue without waiting, and SQL will assume that the log record *will* be written. You may know the ACID rules (Atomicity, Consistency, Isolation, Durability) that guarantee that when a transaction is committed it stays committed. Allowing delayed durability basically removes the D. Normally Durability is achieved through write-ahead logging (WAL) which means that transactions are written to disk before the transaction is complete. With delayed durability enabled you are trading durability for performance. So, be aware that you can lose data
SQL uses 60KB of the log buffer, and will attempt to flush the log to disk when the 60KB block is full. There is a risk of data loss with this. Delayed Durability option is set at the database level, at the individual transaction level, or (for natively compiled procedures in In-Memory OLTP) at the procedure level. It is best to enable if your transaction log is a bottleneck and you are experiencing high WRITELOG waits.
If you need to you can force the transaction log to flush delayed transactions by executing sys.sp_flush_log.
ALTER DATABASE <dbname> SET DELAYED_DURABILITY = DISABLED | ALLOWED | FORCED
Add to the transaction commit….
COMMIT TRANSACTION TransactionName WITH (DELAYED_DURABILITY = ON)
In-Memory OLTP (EE only)
Add to the BEGIN ATOMIC block…
CREATE PROCEDURE [dbo].[NewRoadRace] @RaceID INT, @RaceName VARCHAR(500), @LengthMiles INT, @State CHAR(2)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
BEGIN ATOMIC WITH (DELAYED_DURABILITY = ON, TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ‘us_english’)
INSERT INTO dbo.RoadRaces (RaceID, RaceName, LengthMiles, [State]) VALUES (@RaceID, @RaceName, @LengthMiles, @State)