top of page

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.


Database Shutdown

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.

3 comments

3 תגובות


אורח
26 במאי 2023

Trust me, there are still good and genuine hackers amongst all, and I can boldly tell you that PARAGON CREDIT SOLUTION is one among the best out there, 2 years ago I messed up with my credit score due to my wife’s illness, and she was diagnosed of stage 3 cancer, I had 9 negatives on my report and my score was below 517, so I tried some companies here in Alabama but I was scammed, not until my father in-law visited us, I told him what I’ve been going through and he promised to recommend a reliable hacker in Texas, after few days, he recommended paragoncreditsolution@gmail.com I was skeptical but he assured me to be positive, after spoke w…

לייק

Eitan Blumin
Eitan Blumin
20 במאי 2023

Thank you for sharing, Guy!

I didn't know this either about planned shutdowns and delayed durability.


Do you know whether it's possible to check for the existence of transactions in delayed durability mode, that would need to be flushed?

לייק
Guy Glantser
Guy Glantser
21 במאי 2023
בתשובה לפוסט של

Good question. I am not aware of such thing.

לייק

STAY IN TOUCH

Get New posts delivered straight to your inbox

Thank you for subscribing!

bottom of page