2

Seems like a simple question but I've found conflicting information out there. Can anyone enlighten me (ideally with a rock-solid source)? Is it:

  1. Part of the transaction log, per database?
  2. Part of tempdb?
  3. Somewhere else...

My gut says the transaction log file.

0

1 Answer 1

3

Part of the transaction log, per database?

Yes that is correct. Undo happens when database goes through crash recovery or recovery. There are 3 phases of recovery

  1. Analysis
  2. Redo
  3. Undo

The undo phase is where transactions that are not committed are rolled back so that they don't end up being in database when it comes online. Yes this information about transaction which is not committed is present in transaction log. To understand more about this please read Logging and recovery in SQL Server

Tempdb is always in simple recovery model but "Minimal Logging" is followed in tempdb. This means ONLY information to rollback transaction is there no information to rollforward the transaction is present. Crash recovery does not happens in Tempdb.

Further Reading.

4
  • 2
    To add to it - crash recovery can't happen in TempDB, because it is recreated on every SQL Server engine restart. Then contents of model database are copied into TempDB, as tested by Jonathan Kehayias: sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/14/… ; Only TempDB's file structure (size, locations) is preserved on restart, like @Shanky wrote some time ago: social.technet.microsoft.com/wiki/contents/articles/…
    – Marcin S.
    Commented Jul 13, 2016 at 10:46
  • If you have enterprise edition, the database is "partially" online after REDO completes with locks taken to protect data/schema that is affected by UNDO.
    – SQLmojoe
    Commented Jul 13, 2016 at 16:42
  • @Shanky I mean in SQL Server - another way of putting it, say a row is changed (not committed) but the datafile has been updated (possible?). A second user wants the pre-updated version, where does it get it from? Undo?
    – dwjv
    Commented Jul 14, 2016 at 14:39
  • If a change is not committed it wont make an entry into data file. When change is done it's information is first written into transaction log file and when committed the commit is fired in transaction log and flushed to log disk then change is made to page making it dirty page. The whole process is explained by Bob Dorr in This Blog. Read the WAL protocol
    – Shanky
    Commented Jul 15, 2016 at 4:47

Your Answer

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

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