SQL Server Transaction Architecture interview questions

What operations do SQL Server transaction logs support?

Operations supported by transaction Logs:-
Recovery of individual transactions
In case an operation fails, a transaction can be rolled back

Recovery of all Incomplete Transactions When SQL Server Is started
In case the SQL server fails, all transactions that were in a state of execution can be recovered. This operation helps to maintain integrity of the database.

Rolling a Restored Database, File, File group, or Page Forward to the Point of Failure
In case of a hardware or disk failure, if any database file is affected; it can be restored to the point after which the failure occurred.

Supporting Transactional Replication
Transactions can be replicated from the transaction log into the distribution database

Supporting Standby-Server Solutions
A primary server sends the active transaction log of the primary database to one or more destinations. This destination is a standby server

Explain the purpose of check points in a transaction log.

Checkpoints are used for backup of transactions. When a checkpoint is introduced in a state and the next state is achieved; if something goes wrong in the new state, the transaction can be returned to the checkpoint. This means that checkpoints allow the transactions to be rolled back.

What is write-ahead transaction log?

A write-ahead transaction log on the disk where modifications are written first before the associated log record is written to disk. This is to maintain the ACID properties. Data modifications are not made to the disk directly. They are made to the buffer cache first. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log.