SQL Server – Where to Find ‘UNDO’ Functionality

sql server

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.

Best Answer

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.

Related Question