Transaction Log Management

ADD New Log File:
If you need to enlarge your transaction log, one method you can use is to add a file to the log.
You can do this by using the ADD LOG FILE clause of the ALTER DATABASE statement.

Shrinking Transaction Log

In order to truncate the transaction log, you must first back up the log. The database engine then automatically truncates the inactive records. However, truncating the log doesn’t reduce its size. Instead, you must shrink the log file, which removes one or more inactive virtual log files.

Transaction Log Truncation:

The process in SQL Server  "Log truncation" deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log.
If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.
The way this truncation happens is dependent on the recovery model that was selected for your database.

Transaction Log Roles in SQL Server

What is logged in the transaction log?


SQL Server logs every event in a database to a more or lesser extent.
•When a transaction begins or ends
•Every update , insert or delete
•Drop and creation of tables and indexes.

Transaction Log Architecture

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.