What are Virtual Log Files in SQL Server Transaction Log File?
- A virtual log file (VLF) is a small, fix-size unit of log space that SQL Server uses internally to manage the physical transaction log files. Each VLF has a start and an end offset that defines its size and position within the physical log file. Virtual log files are created when the physical log file is first initialized, and additional virtual log files creating as the physical log file grows.
- The number of virtual log files and their size is directly related to the growth increment of the physical log file. For example, if the physical log file is set to grow by 1 MB at a time, each VLF will be 1 MB in size. If the physical log file is set to grow by 10 MB at a time, each VLF will be 10 MB in size.
- When a transaction is committing, SQL Server writes a record to the transaction log indicating that the transaction is committing. This record is a committing record. The commit record contains information about which transactions committing and the location of those transactions within the virtual log files.
- When a transaction is rolled back, SQL Server writes a record to the transaction log indicating that the transaction is rolled back. This record is a rollback record. The rollback record contains information about which transactions are rolling back and the location of those transactions within the virtual log files.
- Virtual log files using SQL Server to track changes made to the database. Each time a change is made to the database, a new virtual log file creating. When the physical log file grows, additional virtual log files creating. SQL Server uses virtual log files to keep track of changes made to the database so that it can recover the database in the event of a failure.
- SQL Server uses two types of recovery models to restore databases: full and simple.
- The full recovery model uses virtual log files to keep track of changes made to the database so that it can recover the database in the event of a failure.
- The simple recovery model does not use virtual log files. Instead, it relies on the backup history to restore the database in the event of a failure.
- When you take a full database backup, all of the virtual log files are being back up. When you take a differential backup, only the virtual log files have changed. Since the last full backup is backing up. When you take a transaction log backup, only the virtual log files that have changed since the last backup are backed up.
- You can view the contents of the virtual log files by using the fn_dblog function. This function takes two parameters: a start virtual log file number and an end virtual log file number. The start virtual log file number is the number of the virtual log file that you want to start with. The end virtual log file number is the number of the virtual log file that you want to end with.
- For example:
- if you want to view the contents of the first 10 virtual log files, you would use the following code:
- SELECT * FROM fn_dblog(NULL, NULL) WHERE Operation LIKE ‘LOP_%’ AND Context IN (1, 2) ORDER BY StartTime DESC
- The output of this query would show you the contents of the virtual log files.
- You can also use the fn_dump_dblog function to view the contents of the virtual log files. This function takes two parameters: a start LSN and an end LSN. The start LSN is the Log Sequence Number of the virtual log file you want to start with. The end LSN is the Log Sequence Number of the virtual log file you want to end with.
Conclusion:
Virtual log files using SQL Server to track changes made to the database. When the physical log file grows, additional virtual log files creating. SQL Server uses virtual log files to keep track of changes made to the database. So that it can recover the database in the event of a failure. There are two types of recovery models that SQL Server uses to restore databases: full and simple. The full recovery model uses virtual log files while the simple recovery model does not. You can view the contents of the virtual log files by using either the fn_dblog or fn_dump_dblog function.