Microsoft has introduced a new feature called Delayed Transaction Durability with its SQL Server 2014 version which aims to reduce the delays happening at the transaction level and to facilitate a full time availability of the database to the front end client application. By default, the transactions in SQL Server are durable which means the committed transactions will remain in the system even after a system failure. With its 2014 version Microsoft has introduced two types of durability in its transactions as follows,
1. Full Transaction Durability and 2. Delayed Transaction Durability.
Full Transaction Durability:
Consider a data entry user using a front end application built by ASP.Net backed by a SQL Server database. When the data entry operator enters 100 records the data gets written into the log file of the database and when he hits submit button from the Client application, the 100 records are moved from the log file into the data file. During this process the user will not get the control of client application until write process from log file to data file gets completed and he wouldn’t be able to process/enter any data at front end level (This corresponds to the processing screen which appears when we submit data over a website – annoying right?).Once the write process is completed, the log file becomes available and the user gets availability of the Front End and continues with his work. This process is called Full Transaction Durability. On the pros side there are no data loss and on the cons side there will be a significant latency at the client side application.
Delayed Transaction Durability:
In this method a buffer is used at the log file and the data is sent periodically to the data file whenever the buffer gets filled up. Imagine if the buffer has the capacity to hold up to 25 records flushes the data to a data file once it gets filled up. Flipping the above said data entry process for this method, the data gets written into the data file when the user enters the 26th record and when he enters the 100th record and hits submit, 75 records were already moved to the data file and only 25 records needs to be moved which will be taken care by the buffer flush and the client application is readily available to the user when the write happens which mean a no wait time. This process is called Delayed Transaction Durability. The latency at client side is significantly reduced and user is given an all-time availability of the client application. The DBA has to configure the database to handle full durable/delayed durable transactions.
Couple of month’s back one of my colleague had an issue of the log file getting filled up at a rapid pace and the nightly jobs were getting failed. I hope configuring the data base with Delayed Transaction Durability will enable periodic flushing of data from log file to data file which will prevent the log file from getting filled up. I am going to recommend this to my colleague. Hope you all will:-)
#JunaithHaja