Log Architecture:
Log file contains log records and internally it will maintain LSN (LOG SEQUENCE NUMBER)
File groups are not applicable for log files since it doesn’t contain data.
The transaction log is a wrap-around file. For example, consider a database with one physical log file divided into four virtual log files. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. Log records in the virtual logs that appear in front of the minimum recovery log sequence number (MinLSN) are deleted, as truncation operations occur. The transaction log in the example database would look similar to the one in the following illustration.
When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.
Log file contains log records and internally it will maintain LSN (LOG SEQUENCE NUMBER)
File groups are not applicable for log files since it doesn’t contain data.
The transaction log is a wrap-around file. For example, consider a database with one physical log file divided into four virtual log files. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. Log records in the virtual logs that appear in front of the minimum recovery log sequence number (MinLSN) are deleted, as truncation operations occur. The transaction log in the example database would look similar to the one in the following illustration.
When the end of the logical log reaches the end of the physical log file, the new log records wrap around to the start of the physical log file.
Each physical log file internally is divided into a number of virtual log files. We can’t predict the size and the no of VLF’s inside the physical log file size.
Min Lsn:
• Minimum Recovery LSN (MinLSN) and is the minimum of the:
• LSN of the start of the checkpoint(144)
• LSN of the start of the oldest active transaction(142)
Active log: The section of the log file from the MinLSN to the last-written log record is called the active portion of the log, or the active log
WAL protocol (Write Ahead login): it guarantees that no data modifications are written to disk before the associated log record is written to disk. (log records must be written to disk prior to data modifications)
We can’t truncate the active log,
In full recovery model log backups will truncate the inactive portion of the log.
In simple recovery model whenever checkpoint occurs log file get truncated.
Dirty pages: pages that have been modified in the buffer cache but not yet written to disk.
Checkpoint: it flushes the dirty pages from buffer cache to disk at regular intervals.
Operations that causes check point to occur:
• A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.
• A minimally logged operation is performed in the database;
• Database files have been added or removed by using ALTER DATABASE..
• An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service.
• A database backup is taken.
• If the database is using either the full or bulk-logged recovery model, it depends up on the recovery interval option.
• If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
• The log becomes 70 percent full.
• It depends on the recovery interval option.
