2

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.

Improve this question
3
  • 2
    Possible duplicate of View delete statements in the Transaction log
    – Erik Darling
    Dec 5, 2017 at 17:11
  • I think my question is different as I have explained it in Edit part.
    – user139992
    Dec 5, 2017 at 17:55
  • From linked article: you wont find the exact scripts that were executed on sql.(in the transaction log)
    – John K. N.
    Dec 6, 2017 at 10:12

1 Answer 1

Reset to default
4

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.

Improve this answer
2
  • 1
    There are already some 3rd party tools that do this that OP might be interested in, like Quest LiteSpeed and Apex Log Reader, but you may have vendor reasons for not bringing them up ;)
    – Erik Darling
    Dec 5, 2017 at 19:30
  • 1
    @sp_BlitzErik Good point! For which I like or have used, no comment ;)
    – Sean Gallardy
    Dec 5, 2017 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.