What are transaction logs and virtual log files (VLF)

The transaction log is an integral part of SQL Server. Each database has a log file that is stored separately from the data file. The transaction log basically records all database modifications.

For example, when a user issues an insert operation (insert), it is logged in the transaction log. If an error occurs, the database can roll back or restore the state before the transaction and prevent data corruption. For example, an employee needs to insert 2000 data into the database, when suddenly the server loses power. Because the INSERT statement has written to the transaction log and knows that an error has occurred, it will roll back the operation. Without this feature, you can imagine that we need data filtering to see how many rows were successfully inserted at the bottom, and then change the code of the other lines to do other things. What's more, if you insert randomly, you have to determine what data has been inserted and what data needs to be excluded. This may take a very, very long time.

Log entries are sequential records in the file. The entire log file is divided into a number of small pieces called virtual log files. When a virtual log file is filled, he automatically inserts it into the next virtual log file. When the virtual log file has reached the end of the entire log file, he will go back to the beginning of the virtual log file and overwrite the previous data. The operation is as follows:

What are transaction logs and virtual log files (VLF)