Sql-server – the use of the transaction log buffer in SQL Server

backupsql servertransaction-log

I have a question like why do we use in transaction log buffer.

Like SQL Server puts the transaction log in the Transaction log buffer, when the checkpoint runs it transfers the dirty pages(committed) as well as logs to the disk (.mdf/.ldf).

So there must be no active log in the actual transaction log file, all the transactions are waiting for backup process.

Thanks in Advance.

Best Answer

Imagine a modification, for instance INSERT of a row.

SQL Server finds the page(s) that needed to be modified and brings them into memory (if not there already).

Then it constructs log records to reflect the modifications that is about to be performed. These are not written directly to the physical ldf file, for performance reasons. They are cached, in "log buffers".

Then the page(s) are modified, also in memory.

When the transaction is about to commit, SQL Server need to first "harden" the log recods. I.e., physically write the log buffers to the ldf file, before the transation is committed. I.e., a synchonous write, seen from the user's perspective.

The actual data pages will be written at a later time. For instance when checkpoint occurs.

Related Solutions

Sql-server – SQL Server – how transactions and transaction log work (simplified)

No your theory is wrong.

Dirty pages can be written to disc even if the transaction is not yet committed. However it is ensured that they cannot be written before the last transaction log entry that modified the page has been written to disc.

The transaction log records do contain sufficient information both for redo and undo (except for in tempdb where only undo is necessary). If you decide to rollback the transaction then nothing is deleted from the log. Instead compensation log records are written to the log indicating this.

Sql-server – Only do log backup if necessary in SQL Server

One alternative here is to calculate the size of active transactions in your transaction log by using DBCC LOGINFO. Your logic would look something like this:

CREATE TABLE #vlfs(
   RecoveryUnitID int
  ,  FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)
);

DECLARE @MBthreshhold int

SELECT @MBthreshhold = 5 --Enter your threshhold in MB here

INSERT INTO #vlfs
EXEC ('DBCC LOGINFO')

IF (select sum(filesize/1024/1024) 
    from #vlfs
    where [Status] = 2) > @MBthreshhold
BEGIN
    BACKUP LOG [foo] to <<backup location>>
END

Please note there are a lot of caveats here. Your transaction log will never be completely empty and there is always something to backup to it. You can use this to determine at what size point you want to actually run your backup, but this can be a HUGE risk to your Recovery Point Objective (RPO). Your log backups will happen at variable times, meaning you can never guarantee the maximum amount of data loss you will incur in a disaster. This sort of strategy should be pursued with extreme caution.

Best Answer

Related Solutions

Sql-server – SQL Server – how transactions and transaction log work (simplified)

Sql-server – Only do log backup if necessary in SQL Server

Related Question