SQL Server – Understanding Transaction Log Records

concurrencyrecoverysql servertransactiontransaction-log

Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records,

  • 1 record for transaction start: T_i start,
  • 1 for transaction changes on write(x) operation: T_i,X,V_1,V_2, where X is the data item that has been changed from V_1(old value) to V_2(new value).
  • And 1 for transaction commit(or abort),T_i commit.

How can I see these log records in SQL Server? how can I see recovery phase (analyses, redo and undo) in SQL Server?

Edit:
The given link in comment, will show me the table log that which executed transaction for example has delete command or update command. I want to show for example which changes Transaction T_i has made to a data item.
For example T_1,A,100,150 means transaction T_1 has changed the value of A from 100 to 150.

Best Answer

Based on Silberschatz's Database system concepts book, log file for a transaction has 3 records

That's the minimum you'd need for replay and recovery of individual transactions without any optimizations. There may be many more than 3 for a single CRUD item in SQL Server.

How can I see these log records in SQL Server?

The one I use to see the active log is sys.fn_dblog (Link to Remus's log post). Note that this function isn't documented and can/will change and doesn't show things such as Hekaton logs which need to be cracked using sys.fn_dblog_xtp which again isn't documented.

how can I see recovery phase (analyses, redo and undo) in SQL Server?

Through many hours of painstaking work. You can use sys.fn_dump_dblog() to look at transaction log backups that are taken after redo and undo has run.

Since the SQL Server Log records and files aren't documented, except for physical and logical architecture, you'll just have to search the internet and make your own best guesses.

If you're asking because you'd like to make software to deal with the log, then you'll need to get in touch with Microsoft Partner Resources. Otherwise, you're on your own, armed with the collective knowledge of the internet and volumes of your favorite beverage.

For example T_1,A,100,150 means transaction T_1 has changed the value of A from 100 to 150.

The SQL Server log isn't really human readable, though the functions help. I stated before, it's not always that easy as change 100 to 150.

Let's say you changed 'Hi' to 'Sean'. This might cause a forwarding record, or a page split. It might be the first record in a table, or a heap, or be changed because of multiple indexes.

I'm not trying to put you off, just that academia and the real world and generally two very different things.

Related Question