I have written quite a while on Delayed Durability and every blog post when I think it will be the last post on this subject, I always get some question in an email and I end up writing a new blog post.
Here are a few blog posts which I have written on this subject. Please read them if you want to understand delayed durability in detail.
- SQL SERVER – Basics of Delayed Durability in SQL Server 2014
- SQL SERVER – How to Setup Delayed Durability for SQL Server 2014?
- SQL SERVER – Simple Example of Delayed Durability
- SQL SERVER – Delayed Durability Database Level and Transaction Level
Here is another blog post based on a question I recently received.
Question) I have been using delayed durability for few of my queries and stored procedures. Is there any way, I can be sure that every single transaction log which is not hardened on the disk so far are flushed to disk?
Indeed a very good question. Well, SQL Server has a stored procedure says. sp_flush_log which guarantees that all the previously committed delayed durability transactions are made durable by flushing them to disk. So at any point of time, you want to be sure that you have flushed all of yours delayed transactions, you can execute this stored procedure.
EXECUTE sys.sp_flush_log
Remember, there is one more thing know here as well. If you execute any fully durable transactions right after delayed durable transactions, it will also automatically flush all the transaction to disk as well.
Reference: Pinal Dave (https://blog.sqlauthority.com)
