2

What does the transaction log file store? Is it the blocks of transactions to be executed, is it the snapshot of records before beginning the execution of a transaction or is it just the statements found in a transaction block?

  • Rolling a restored database, file, filegroup, or page forward to the point of failure. Recovery of individual transactions is benefit of Transaction log. – Md Haidar Ali Khan Apr 19 '15 at 11:40
  • Have you read the documentation? – vonPryz Apr 19 '15 at 13:43
  • In addition to what @MdHaidarAliKhan said, the transaction log is also used during crash recovery to return the database to consistent state without data loss. The tran log records will vary depending on the operation and recovery model but, at a high level, it will contain the minimal information needed to undo/redo transactions. – Dan Guzman Apr 19 '15 at 13:50
  • @DanGuzman, Exactly you are right. I had explain few things about transaction log. but that is also right towards T-log. – Md Haidar Ali Khan Apr 19 '15 at 13:58
  • @MdHaidarAliKhan I understand how we can use it to recover. But what does it store? Does it store real data available in the database at a point of time or does it just store the statements? Like if there are 100 records, is the values of 100 records or the insert queries for 100 records? – mayooran Apr 19 '15 at 14:44
5

The transaction log stores every transaction and the changes made by those transactions to the database. This answer gets very complicated when going into detail but generally it stores the following.

  • Modification Operations
  • Log sequence numbers (LSN)
  • Row data. Before/After/Partial/Meta-data, depending on the operation.
  • Reserved space for rollbacks

It does not store queries or t-sql statements. Please refer to this overview by Microsoft.

https://msdn.microsoft.com/en-gb/library/ms190925.aspx

The specific format is not fully documented and is subject to change betwen versions.

  • This means, if there is a table and if insert queries were executed on it, what would be stored is the values of those insert queries right? – mayooran Apr 19 '15 at 22:52
  • Yes but think about the resulting data change to the data file (.mdf/.ndf). For an insert it stores the data change to each index. – Russell Hart Apr 20 '15 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.