Abstract :
In this article we learn Role of log file in
Transaction Management in SQL server database 2008.
Agenda :
1) What is transaction Log?
2) What happen in log file when commit and
Rollback transaction?
3) 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.
Abhijit Patil is a Founder of Dotnetbyabhipatil ,he Passionate on Technology ,He have 3 Award on dotnetspider Including Golden. He got 2nd Time MVP(Most Valuable Professional) award at Delhi International Conference on C# Corner. also Chapter Lead of C# Corner Pune Chapter.
ConversionConversion EmoticonEmoticon