Here is small effort to explain the architecture of SQL Server transaction log which is depends on log file, Log file is known as write ahead log and capture DML and DDL related information. Whenever any large transaction runs, log file start taking all space on disk, once backup runs log file goes back to initial size. This is because of transaction log architecture design. Lets understand architecture internals and how this log and virtual log files works together in this article.
Transaction log architecture and virtual log files
Transaction log is reusable circular file which captures transaction details to recover database in disaster. SQL transaction Log file is divided into number of virtual log files (VLF) to handle active and inactive transaction in the database. SQL server transaction log writes entries sequentially into the virtual log files one by one. When one file is full, SQL move to next virtual log file (VLFs) till it reaches to last one. After reaching at the end, it will go back to first virtual log file which in inactive state and start using again.
Virtual Log file can be either in active or inactive states
What is Active state in Virtual Log ?
The log records that are currently used by sql server database engine, it can contain more than one virtual log files that we call it as active virtual log file. The log records may part of active transactions, replication, mirroring or log shipping. Active virtual log may be part of transaction which is already completed but not yet written back to the data file.
What is Inactive state in Virtual Log ?
The log records that are written back to data file and also not in use by database engine is known as inactive virtual log file. The log records may not be part of active transactions, replication, mirroring or log shipping. Whenever log backed up in full and bulk logged recovery model, virtual log file becomes inactive.
How to reuse transaction log?
Transaction log is cyclic process of writing log record into virtual log file by SQL Server, whenever active virtual log file filled up with data it moves to next inactive virtual log file. In short it needs inactive virtual log file for writing log records. If all virtual log files are filled up with active data then it will grow further to create more virtual log file. If it cannot grow then it will throw error saying transaction log for database is full and transaction will fail.
To find the reason why transaction log cannot be reuse, execute the below query
Select log_reuse_wait_desc from sys.databases where name = ‘yourdbname’
To reuse virtual log files, we have to truncate logs. After truncation it will release space and mark as inactive virtual log which is ready to use by SQL.
Let’s understand with the SQL Server Transaction log architecture diagram, assuming below is the transaction log file which divided into 5 virtual log files (v1, v2, v3, v4 and v5). In this V1 and V2 are in inactive state and V3, V4 and V5 are in active state which associated with active transaction in database.
After number of data modification in log records, SQL moves to the last virtual log file (V5). Now it will look for inactive virtual log file for writing more log records so it will go back to first inactive virtual log file (V1) and start writing log records in V1 and V2. Here we can see that V3, V4 and V5 are in still active state. Now to mark them as inactive, log backup should be run that will release some space for SQL to reuse them.
Otherwise if log records keep on growing and finished with writing in V1 and V2 virtual log files then it will throw error saying transaction log for database is full.