Delayed Durability - Getting Started with SQL Server 2014 Administration - page 50

Delayed Durability and transaction
log flush
Delayed Durability means that it is possible that there will be transactions that have
been reported as committed, and yet aren't written or hardened to the database
transaction log. You know this and should be prepared to accept the potential data
loss should the system crash or failover. But what if you need to take the system
down for some reason? You wouldn't want to lose data as a result of planned outage.
So, if your database is using Delayed Durability, you need some way of manually
flushing the log to the disk. With SQL Server 2014, there are two ways to do this:
• You can execute the system-stored procedure called sp_flush_log . This will
flush all committed transactions that are using the Delayed Durability option
to the disk. To force a full flush of the in-memory log to the disk, execute the
command exec sp_flush_log .
You can also execute a fully durable transaction that makes changes to the same
database. This will also force a flush of committed delayed durable transactions to
harden to the log.
To force a full flush of the in-memory log to the disk, you can execute the following:
exec sp_flush_log
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))