SQL Server Transaction Log — Part 2

This is the second article in the series dedicated to SQL Server transaction log and its specificities. Here we are going to examine the log record details.

Log Records

Log records are the core of the logging and recovery mechanisms. A log record describes a single change in a database. Thus, every change to a database has a log record or log records that help to describe that particular change.  Although you do not need to understand log record details, to understand what is going on with logging and recovery, those details are extremely interesting.

The log record has a unique Log Sequence Number that we defined in the first article. The Log Sequence Number allows the log record to be found in the transaction log. In addition, every data-file page has an LSN in its page header that identifies the most recent log record whose change is reflected on the page. This is critical for crash recovery.

Log records for concurrent transactions are intermingled in the transaction log according to when they occurred in time. Log records are stored in log blocks in the buffer pool until they are flushed to disk.

There are no non-logged operations in user or system databases. However, there is an exception: in tempdb, version store and work file operations are non-logged. Log records never move in the transaction log.

What is Inside a Log Record?

Information in a log record allows it to be redone (rolled-forward) or undone (rolled-back). This ability of a log record is crucial for allowing transactions to be rolled back, and for recovery work. Log records contain many fields, depending on the type of log record. There are some fields common between all records, including:

• The log record type
  • begin transaction log record
  • commit transaction log record
  • allocating a page by changing the allocation bitmap
  • inserting a row
  • deleting a row
  • modifying a row
• The context of the log record, if any.
• The transaction ID the log record is part of if any. Most log records are part of transactions.
• The log record length. Log records usually have a fixed size, and then depending on the amount of data that is in the log record, there will be a variable portion as well.
• The LSN of the previous log record in the same transaction. If any. The LSN is essentially a pointer to the previous transaction log record that was generated by that particular transaction. This chain of the previous LSNs allows that particular transaction to be rolled back, because the rollback is done in the reverse order, starting at the most recent log record.
• The amount of log space reserved in case the log record must be undone.

Log Space Reservation

Every log record that is generated in the forward part of a transaction must reserve free space in the transaction log to allow the log record to be rolled back, without the transaction log having to grow.

The log space reservation mechanism is very conservative, always reserving enough space, and usually more, just in case an unexpected situation occurs. For example, an update or delete on a compressed table will reserve more space than a similar update or delete on a non-compressed table. This happens because the rollback of the update on the compressed table may have to cope with the updated row no longer being on a compressed page, and so would have to write full-width columns in the log record instead of compressed data.

Log Record Types

There are many types of log records, including:

• LOP_FORMAT_PAGE The log format page operation — is where a page has been formatted, which means it’s header has been created. The log record is going to log at least the page header and potentially some more contents of the page if the page has been created and filled in as part of an operation like an index build or rebuild)
• LOP_MODIFY_ROW This operation is changing a small portion of an existing data.
• LOP_SET_BITS This log record applies to allocation bitmaps.
• LOP_INSERT_ROWS and LOP_DELETE_ROWS
• LOP_SET_FREE_SPACE Applies to PFS – the allocation the bitmap that keeps track of the allocation statuses of pages.

Any log records that are going to make a change to a data page or an index page in a tabular index include:

• • The allocation unit ID
  • The page ID and slot ID of the record on the page, which is essentially the zero-based record ID of the data or index record on the page.
  • The after-image, or the before-image and after-image of the changed data. There may be multiple sets of these in a single log record. After-images allow redo to occur. Before-images allow undoing to occur.

Lock Logging

Some log records include a bitmap of which locks were held when the described change took place. The bitmap contains:

• • • Count of the number of locks.
    • What type and mode of lock – for instance, a page lock in X mode.
    • What the lock is on

During crash recovery and database mirroring/availability group failovers, these locks will be acquired for all log records that are going to be undone. This allows the fast recovery feature in Enterprise Edition from SQL Server 2005 onwards.

Log Records in Transactions

All transactions generate at least three log records, always in the following sequence:

• • • • LOP_BEGIN_XACT – includes information like the SPID, transaction name, and start time. All transactions started by SQL Server have names to describe the operation (e.g. AllocFirstPage, DROPOBJ)
      • Other records for the transaction.
      • LOP_COMMIT_XACT – if the transaction commits.
      • LOP_ABORT_XACT – if the transaction rolls back.

These both include the end time for the transaction.
Log records in a transaction are linked together backwards by LSN. This means that next log record that is generated for a transaction has the LSN of the previous log record that was generated for this particular transaction. This allows the transaction to be rolled back correctly.Some log records are non-transactional at all, including:

• • • • PFS free space changes (impossible to reconcile with other transactions)
      • Differential bitmap changes (one-way change only)

Examining Log Records

There are two ways to examine log records. You can use DBCC LOGINFO function, but it is recommended to use the fn_dblog table-valued function. It has extremely simple syntax:

SELECT * FROM fn_dblog (startLSN, endLSN);
GO

It is extremely powerful function because it:

• • • • returns a tabular result set that can easily be managed.
      • allows complex predicates to be used.
      • scans all transaction log in the active portion of the log, from the start of the oldest uncommitted transaction to the most recent log record. This can be overridden using trace flag 2537

The startLSN and endLSN fields are usually passed as NULL
Here is the demo:

USE DBTest2014
GO
 
SET NOCOUNT ON;
GO
 
--Set the SIMPLE recovery mode with no auto-stats
-- to avoid unwanted log records
ALTER DATABASE DBTest2014 SET RECOVERY SIMPLE;
ALTER DATABASE DBTest2014 SET AUTO_CREATE_STATISTICS OFF;
 
CREATE TABLE [TEST_TABLE] ([C1] INT, [C2] INT, [C3] INT);
 
INSERT INTO [TEST_TABLE] VALUES (1,1,1);
GO
 
--Clear out the log
CHECKPOINT;
GO
 
-- Implicit transaction
INSERT INTO [TEST_TABLE] VALUES (2,2,2);
GO
 
SELECT * FROM fn_dblog(null, null);
GO

Here is the shortened result set. Actually fn_dblog returns a variety of different records like Log record Length, Flag Bits, Log Reserve, AllocUnitId, PageID, SlotID, Previous Page LSN and others.

Modifying Row Contents

Log modifications are logged in two ways: as LOP_MODIFY_ROW or LOP_MODIFY_COLUMNSrecord. No matter which method is used, it will log the bytes that are actually being changed. For instance, changing an INT value from 1 to 24 only logs one byte of change as the other three zero-bytes did not change. SQL Server will use an LOP_MODIFY_ROW log record if there is a single portion of the row being updated. A portion is defined as follows: each variable-length column in the row is a “portion” and the entire fixed-width area of the row is a “portion”, even if multiple columns are being updated, but only if the bytes being updated are 16 bytes or less apart in the row.

LOP_MODIFY_ROW contains:

• Before image
• After image
• Index key columns if applicable
• Lock bitmap

LOP_MODIFY_COLUMNS contains:

• Before and after offsets array
• Length array
• Index key columns if applicable
• Lock bitmap
• Before and after image pairs

Compensation Log Records

This is a special kind of log records that are used to help a transaction rollback. When a transaction rolls back, the change described by each log record in the transaction must be undone in the database. Rollback starts with the most recent log record for the transaction and follows the previous LSN links until the LOP_BEGIN_XACT log record. For each log record:

• Perform the “anti-operation” that will negate the effects of the log record
• Generate a log record, marking it as a COMPENSATION log record, as it is compensating for the log record in the forward part of the transaction.
• The COMPENSATION log record’s previous LSN points to the log record prior to the one it is compensating for. It essentially causes the log record to no longer is part of the chain of log records for the transaction.
• The reserved log space for the log record is released

COMPENSATION log records cannot be undone, only redone.

Rolling Back a Transaction

Here is a graphical representation of what is going on when a transaction rolls back:

Let’s examine the following code:

USE DBTest2014
GO
 
SET NOCOUNT ON;
GO
 
ALTER DATABASE DBTest2014 SET RECOVERY SIMPLE;
ALTER DATABASE DBTest2014 SET AUTO_CREATE_STATISTICS OFF;
 
CREATE TABLE [TEST_TABLE] ([C1] INT, [C2] INT, [C3] INT);
 
INSERT INTO [TEST_TABLE] VALUES (1,1,1);
INSERT INTO [TEST_TABLE] VALUES (2,2,2);
GO
--Clear out the log
CHECKPOINT;
GO
-- Explicit transaction to insert a new record
BEGIN TRAN;
INSERT INTO [TEST_TABLE] VALUES (3,3,3);
GO
 
SELECT * FROM fn_dblog(null, null);
GO
--Roll it back
ROLLBACK TRAN;
GO
 
SELECT * FROM fn_dblog(null, null);

Here we can see a special log record with the description “COMPENSATION”

If we look at previous LSN, we can see that LOP_INSERT_ROWS that we did, links back to …0f40:0001 and this is the BEGIN transaction because the forward part of the transaction links back to the previous log record. The LOP_DELETE_ROW compensation log record does not link back to a record it is compensating for — it links to it (to the BEGIN transaction log record).

So DELEDE has compensated for INSERT and removed it from the list of log records. TheLOP_ABORT_XACT is the signal that the transaction is ended with the rollback. Also, you can see that LOP_ABORT_XACT links back to the LOP_BEGIN_XACT.
When we do a compensation log record, the Log Space Reservation goes down [-74]. So it is actually giving back some space that was reserved for the forward part of the transaction (LOP_INSERT_ROWS [178]). As you can see the log space reservation system is very conservative — the INSERT reserves more space than the DELETE gives back.

Rollbacks and Differential Backups

If a database has a full backup taken, then a transaction updates 100.000 records but the transaction is rolled back, why does a differential backup so much data? Surely the transaction rollback means that nothing changed? The puzzle piece missing here is that rolling back a transaction does not wipe out all changes made by the transaction. As we have seen, the rollback has to generate compensation log records, because the rollback has to generate other changes to compensate for the forward part of the transaction. The page headers of all affected pages were changed at least twice. One to update the page’s LSN for the forward part of the transaction and once to update the page’s LSN for the rollback part of the transaction. In both cases, the update will cause the extent to be marked as changed in the differential bitmap. It does not care what the change was, just that something in the extent changed. There is no way to exclude these extents from the differential backup.

Summary

In this article, we have looked at the log records. Log records are the core of the logging and recovery mechanisms. Each change in a database has a log record associated with it. Each log record describes a small change. A large change has multiple log records inside a single transaction. There are many different types of log records and we have looked at few of them.

The transaction log is an essentially huge topic and a couple of articles is not sufficient to unveil all the details. So if you want to get more detailed information, I would suggest you to read the following book: SQL Server Transaction Log Management by Tony Davis and Gail Shaw and this article: Transaction Log Management.

Also read:

Dive Into SQL Server Transaction Log — Part 1