SQL Server Transaction Log

What is Transaction Log

Transaction Log is used to record all modification in a SQL server database. Results of read only transactions will not be recorded.

For performing an operation on a database, SQL server reads the data into its memory buffer and then performs modification in the data in memory buffer. When the memory buffer becomes full or when the transaction is committed, SQL server records the transaction into transaction log. When the transaction is committed, transaction will be recorded into transaction log and then it will be written into database. Since SQL server always records all transactions into Transaction log before actually writing to database, Transaction log is also know as write-ahead-log.

Can Transaction log be deleted or shrinked ?

If we delete a transaction log which contains records of uncommitted or running transactions, it will result in lose of data. Long running transactions will result in large transaction log files and such transaction log files can not be deleted or shrinked since it contains data of uncommitted transactions.

Purpose of Transaction Log

Transaction Log is used to record uncommitted transactions before actually writing the transaction to database.

Transaction Log is used for automatic recovery of data in case of an accidental system failure or unclean shutdown of SQL server.

Some times transaction log can also got corrupted, an example will be file corruption due to power failure. In such case, If we need to fully restore a SQL server database to a point of time in the past, we need to restore SQL database along with the Transaction Log.