Delayed Durability and potential data loss

In this section, you will discover how it is possible for you to lose data contained in a committed transaction using Delayed Durability.

The following script will create a database called DB1 on a SQL Server 2014 instance:

--Create database DB1
CREATE DATABASE [DB1]
GO

Then you will create a table in the DB1 database called t1. It will be a simple table with two columns called ID and Name:

USE DB1
GO
CREATE TABLE t1 (
  ID int,
  Name Varchar(20))

The following script inserts two rows into the table t1:

USE [DB1]
GO

INSERT INTO [dbo].[t1]
           ([ID]
           ,[Name])
     VALUES
           (1,'Seth'),
           (2,'Jake')
GO

The following script will change the database level Delayed Durability option to Forced. Therefore, all transactions will use Delayed Durability:

USE [master]
GO
ALTER DATABASE [DB1] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT
GO

We will then set up a performance monitor and configure it to monitor log flushes. I am going...