Agenda
- What is transaction Log?
- What happen in log file when commit and Rollback transaction?
- How SQL Server get data from Log file?
Lets learn in details.
What is transaction log ?
Every SQL server database has a transaction log that records all transaction and the database modification made by each transaction. The transaction log must be truncated on a regular basic to keep it from filling up.
The transaction log is a critical component of the database and if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramification of doing this.
When you create a transaction using begin transaction command then a transaction will be created a unique id will assign to the transaction and entry will be written to the log file indicating that transaction is created along with transaction Id.
What happen in log file when commit and Rollback transaction?
Commit
When you commit transaction then an entry will be written to the Log file indicating the transaction is committed along with transaction Id and uses after every change in the transaction to commit transaction. In this case changes in the transaction are executed from top to bottom.
Rollback
When you rollback the transaction then an entry will be written to the log file indication the transaction is rollback along with transaction Id and uses before image of every change in transaction to rollback transaction. In this case the changes are executed to bottom to top.
How SQL Server get data from Log file ?
Whenever SQL Server start it will go through the log file of every database and commit all changes or transaction that have committed entry and all the remaining transaction with rollback entry or the transaction with no rollback and committed entry will be rollback.
Conclusion
Hence we learn Role of log file in Transaction Management in SQL Server 2008.