SQL Server Transaction Log Operations

SQL Server records every significant database change as a log operation inside the transaction log. These operations include transaction control records such as LOP_BEGIN_XACT, LOP_COMMIT_XACT, and LOP_ABORT_XACT; data modification records such as LOP_INSERT_ROWS, LOP_DELETE_ROWS, and LOP_MODIFY_ROW; allocation records such as LOP_ALLOCATE_PAGE; and recovery-related records such as LOP_COMPENSATION and checkpoint records. Together, these log operations enable SQL Server's Write-Ahead Logging architecture, rollback functionality, crash recovery, replication, Always On Availability Groups, log shipping, and point-in-time recovery. For day-to-day troubleshooting, the most important records to recognize are transaction start, commit, rollback, insert, update, delete, compensation, and checkpoint operations because they explain how SQL Server tracks and recovers transactional work internally.

We can categories these into below category

Ø  Transaction Control Operations

Ø  Data Modification Operations

Ø  Page-Level Operations

Ø  Allocation Operations

Ø  Index Operations

Ø  Metadata Operations

Ø  Bulk Operations

Ø  Locking / Transaction Recovery Operations

Ø  Checkpoint Operations

Transaction Control Operations

These are the most commonly asked interview log records.

Log Operation	Meaning
LOP_BEGIN_XACT	Transaction Started
LOP_COMMIT_XACT	Transaction Committed
LOP_ABORT_XACT	Transaction Rolled Back
LOP_PREP_XACT	Prepared transaction (Distributed Transactions)
LOP_FORGET_XACT	Distributed transaction completed

Data Modification Operations

These record actual row changes.

Log Operation	Meaning
LOP_INSERT_ROWS	Insert Row
LOP_DELETE_ROWS	Delete Row
LOP_MODIFY_ROW	Update Existing Row
LOP_MODIFY_COLUMNS	Update Multiple Columns
LOP_SET_BITS	Bitmap Changes
LOP_RESET_BITS	Bitmap Reset

Page-Level Operations

SQL Server stores data in 8KB pages. These operations affect pages.

Log Operation	Meaning
LOP_FORMAT_PAGE	New Page Formatted
LOP_MODIFY_HEADER	Page Header Modified
LOP_DELETE_SPLIT	Page Split During Delete
LOP_INSERT_SPLIT	Page Split During Insert
LOP_EXPUNGE_ROWS	Remove Ghost Records

Allocation Operations

Used when SQL Server allocates or deallocates pages/extents.

Log Operation	Meaning
LOP_ALLOCATE_PAGE	Allocate New Page
LOP_DEALLOCATE_PAGE	Deallocate Page
LOP_ALLOCATE_EXTENT	Allocate Extent
LOP_DEALLOCATE_EXTENT	Deallocate Extent

Index Operations

Very important for senior-level interviews.

Log Operation	Meaning
LOP_HOBT_DELTA	Heap/B-Tree Metadata Change
LOP_DELETE_SPLIT	Index Page Split
LOP_INSERT_SPLIT	Index Page Split
LOP_MODIFY_ROW	Index Row Updated

Metadata Operations

Affect system tables and metadata.

Log Operation	Meaning
LOP_MODIFY_ROW	Metadata Row Modified
LOP_INSERT_ROWS	Metadata Row Inserted
LOP_DELETE_ROWS	Metadata Row Deleted

Bulk Operations

Used during minimal logging.

Log Operation	Meaning
LOP_FORMAT_PAGE	Format New Page
LOP_SET_BITS	Allocation Bitmap Update
LOP_HOBT_DELTA	Index Structure Update

 

 Locking / Transaction Recovery Operations

Common DBA interview topic.

Log Operation	Meaning
LOP_COMPENSATION	Undo Operation During Rollback
LOP_ABORT_XACT	Rollback Completed
LOP_BEGIN_CKPT	Checkpoint Started
LOP_END_CKPT	Checkpoint Completed

Checkpoint Operations

Important for recovery.

Log Operation	Meaning
LOP_BEGIN_CKPT	Checkpoint Started
LOP_END_CKPT	Checkpoint Finished