Delayed Durability During Shutdown

Introduction

In the world of database management systems, ensuring data durability is of paramount importance. It involves guaranteeing that committed transactions are not lost in the event of unexpected failures or system crashes. SQL Server offers a feature known as Delayed Durability to address this challenge. Delayed Durability provides a mechanism for optimizing transaction logging and improving performance without compromising data integrity. This article aims to delve into the concept of Delayed Durability in SQL Server, specifically focusing on its behavior during expected shutdown scenarios.

Delayed Durability is a feature introduced in SQL Server 2014 and later versions that allows transactions to be committed quickly, deferring the durability of the transactional changes to a later point in time. By default, SQL Server ensures durability by writing transactional log records to disk immediately before confirming the transaction commit. However, with Delayed Durability, the transactional log records are kept in memory for a longer duration, reducing the I/O overhead associated with disk writes.

Advantages of Delayed Durability

1. Improved Throughput: Delayed Durability can significantly enhance the throughput of high-volume transactional workloads. By reducing the disk I/O operations required for transaction logging, it minimizes the latency associated with synchronous writes, leading to improved overall performance.

2. Reduced Lock Contention: In scenarios where contention arises due to exclusive locks held by transactions during the commit phase, Delayed Durability can alleviate the problem. With reduced disk I/O, the contention time decreases, resulting in a smoother execution of concurrent transactions.

3. Lower Latency for Transaction Confirmation: By reducing the disk write latency, Delayed Durability shortens the time required to confirm a transaction, leading to faster response times for applications.

What Happens During Shutdown?

Of course, the implications of using Delayed Durability are that you might lose data. Even after a transaction has been committed, if it hasn't persisted yet and there was an unexpected shutdown, then it's lost.

This is probably not an option for many workloads, but for some specific workloads that can tolerate a certain amount of data loss, like application logs, this can be a great feature to reduce transaction latency and improve throughput.

So it's clear what happens when there is an unexpected shutdown, but what happens when the shutdown is planned? You might expect that during a planned shutdown, SQL Server will ensure that all pending transactions are committed to disk before allowing the shutdown process to complete so that committed transactions are not lost during the expected shutdown.

It might surprise you (as it surprised me) that this is not the case. SQL Server doesn't bother to flush the log buffer of the databases during a planned shutdown process, so you still might lose data. The same happens when you perform a failover using any of the high-availability solutions, such as Availability Groups, Failover-Cluster Instances, or Log Shipping.

If you can't tolerate any data loss during a planned shutdown, then you probably can't tolerate data loss at all, and Delayed Durability is not the right choice for you. But if you still choose to use Delayed Durability, and you would like to ensure that you don't lose any data during a planned shutdown (or failover), then make sure you execute the sys.sp_flush_log system stored procedure on each one of the relevant databases before you execute the shutdown command.

Conclusion

Delayed Durability in SQL Server provides a valuable optimization technique for improving transactional performance while maintaining data integrity. During an unexpected shutdown, you might lose some data. But you should be aware that the same behavior allowing data loss also happens during a planned shutdown or a planned failover.