A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.
A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file.
Writing can occur only into one file at the time, so parallel I/O operations are not possible.
Having multiple transaction log files is recommended only if the first one is full or the disk drive is running out of space.
SQL server uses the transaction log in sequential manner. As the data file divided into pages, log files are divided into virtual log file(VLF).
The size of the VLFs in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.
Growth less than 1MB =2 VLF
Growth upto 64 MB = 4 VLF
From 64 MB to 1 GB = 8 VLF
Larger than 1 GB = 16 VLF
To find out the number of VLF in database log file, we can make use of DBCC Loginfo command.
DBCC loginfo('DBName')
Every VLF has a sequence number, which serves to uniquely identify the VLF within the transaction log. The sequence number increase by one every time the log management system activates the next VLF.
When you first create a new database the VLF sequence numbers of the VLF don’t start at 1. They start with whatever the highest VLF sequence number is in the model database transaction log, plus 1.
You might think it is possible to run out of VLF sequence numbers, but it is not. In fact, SQL Server has code that will force the instance to shutdown if a VLF sequence number ever wraps around to zero (if the next VLF sequence number is ever less than the previous one).
Again virtual Log Files are divided into variably sized log blocks. The minimum size of the log block is 512 bytes and log blocks grow up to a maximum size of 60 KB. Log Blocks are acting as containers for the log records.
The log block size is set when one of the following things occurs:
•A transaction generates a log record to commit of finish aborting a transaction.
•The log block size reaches 60KB without a transaction committing or aborting.
There are log records inside a log block (colored on the diagram). Log records are also variably sized. The diagram also shows that log records from multiple concurrent transactions can exist within the same log block. The log records stored in the order written, in a similar manner to a data-page file.
Logically transaction log is a set of log records. Each records is identified by a unique log sequence number (LSN).
The new log records is always written at the logical end of log file with a LSN which is greater than the previous one.
Each LSN will be associated with a transaction id , which can be used to group the log records of a transaction.
As log file store the log records in the sequential order as it happens, It is not necessary that, log records of a transaction are always available in sequence in the log file.
Each log records will have the LSN of previous log as a backward pointer and that will help in rollback of transaction.
Transaction log will store separate log entries for each operation.
For example, while inserting a record into a table, transaction log will store separate log entry for inserting into clustered index and other non clustered index.
In the same way, if a single update statement is updating 10 records, transaction log will capture 10 separate log entries.
LSN is 10 bytes in length and The LSN is shown as a three part structure:
LSN = <VLF sequence number [4bytes]>:<log block number [4bytes]>:<slot number inside the log block\log record #[2 bytes]>.
EX: 00000014:000001ab:0001
Note: 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.
Now our transaction log will looks like below:
A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file.
Writing can occur only into one file at the time, so parallel I/O operations are not possible.
Having multiple transaction log files is recommended only if the first one is full or the disk drive is running out of space.
SQL server uses the transaction log in sequential manner. As the data file divided into pages, log files are divided into virtual log file(VLF).
The size of the VLFs in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.
Growth less than 1MB =2 VLF
Growth upto 64 MB = 4 VLF
From 64 MB to 1 GB = 8 VLF
Larger than 1 GB = 16 VLF
To find out the number of VLF in database log file, we can make use of DBCC Loginfo command.
DBCC loginfo('DBName')
Every VLF has a sequence number, which serves to uniquely identify the VLF within the transaction log. The sequence number increase by one every time the log management system activates the next VLF.
When you first create a new database the VLF sequence numbers of the VLF don’t start at 1. They start with whatever the highest VLF sequence number is in the model database transaction log, plus 1.
You might think it is possible to run out of VLF sequence numbers, but it is not. In fact, SQL Server has code that will force the instance to shutdown if a VLF sequence number ever wraps around to zero (if the next VLF sequence number is ever less than the previous one).
Again virtual Log Files are divided into variably sized log blocks. The minimum size of the log block is 512 bytes and log blocks grow up to a maximum size of 60 KB. Log Blocks are acting as containers for the log records.
The log block size is set when one of the following things occurs:
•A transaction generates a log record to commit of finish aborting a transaction.
•The log block size reaches 60KB without a transaction committing or aborting.
There are log records inside a log block (colored on the diagram). Log records are also variably sized. The diagram also shows that log records from multiple concurrent transactions can exist within the same log block. The log records stored in the order written, in a similar manner to a data-page file.
Logically transaction log is a set of log records. Each records is identified by a unique log sequence number (LSN).
The new log records is always written at the logical end of log file with a LSN which is greater than the previous one.
Each LSN will be associated with a transaction id , which can be used to group the log records of a transaction.
As log file store the log records in the sequential order as it happens, It is not necessary that, log records of a transaction are always available in sequence in the log file.
Each log records will have the LSN of previous log as a backward pointer and that will help in rollback of transaction.
Transaction log will store separate log entries for each operation.
For example, while inserting a record into a table, transaction log will store separate log entry for inserting into clustered index and other non clustered index.
In the same way, if a single update statement is updating 10 records, transaction log will capture 10 separate log entries.
LSN is 10 bytes in length and The LSN is shown as a three part structure:
LSN = <VLF sequence number [4bytes]>:<log block number [4bytes]>:<slot number inside the log block\log record #[2 bytes]>.
EX: 00000014:000001ab:0001
Note: 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.
Now our transaction log will looks like below:
Excellent Information about TLog
ReplyDelete