Control Transaction Durability

By Admin at 5 Jul 2016, 17:29 PM
  • Writing transaction log entries to the disk is required for transactions to be durable. There are two ways to commit transactions: full durability (the default setting in SQL Server) or delayed durability.

    Fully durable transactions are committed only after the transaction’s log records are written to the disk. It is immediately considered durable once it is committed. In contrast, delayed durable transactions are committed while log records are being written, but it is not considered “durable” until the record is completed and flushed to the disk.

    Full durability transaction

    Full durability transactions are logged to the disk before they are committed. This method should be used when you can’t afford any data loss.

    Delayed durability transactions

    Delayed durability transactions are logged to the disk at the same time that the transaction is committed. It may result in some data loss. If performance is considered to be a priority over data loss, then we could use delayed durability.

    Controlling delayed transaction durability

    1.Database Level: use ALTER DATABASE to set the delayed durability setting

    1. ALTER DATABASE <Database Name> SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

    • Disabled: (Default) All transactions are processed as fully durable.
    • Allowed: It will allow each transaction to select between full or delayed durability
    • Forced: Each transaction is committed to the database through delayed durability

    Control Transaction Durability

    2. Transaction Level

    Transaction level durability will have no impact if it’s not enabled at the database transaction level. Ensure that delayed durability is ALLOWED at the databased level.

    1. COMMIT TRANSACTION <Name of transaction> WITH (DELAYED_DURABILITY = ON);

    Natively Compiled Stored Procedure

    In the stored procedure, configure DELAYED_DURABILITY with ON or OFF.

    1. CREATE PROCEDURE <Procedure Name>
    2. WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
    3. AS BEGIN ATOMIC WITH
    4. (
    5. DELAYED_DURABILITY = ON,
    6. < Other code……>
    7. )
    8. END

    Comments

     

    Post a comment

    Please correct the following: