LOG file architecture?

“Transactionlog” Logical Architecture:

Each log record is identified by a unique number called LSN (Log Sequence Number). A log record contains the LSN, TransactionID to which it belongs and data modification record.

Data modification record: It’s either operation performed or before and after data image

• When recorded “Operation Performed”

• Transaction committed – Logical Operation is permanently applied to the data

• Transaction rollback – Reverse Logical Operation is applied to the data.

• When Recorded “Before and After Data Image”

• Transaction committed – Applied the after transaction image

• Transaction rollback – Applied the before transaction image

“Transactionlog” Physical Architecture:

The transaction log is used to guarantee the data integrity of the database and for data recovery.

The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.

The only time virtual log files affect system performance is if the log files are defined by small size and growth_increment values. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database startup and also log backup and restore operations.