Biggest 'holy shit' moment in a game.

[Comrade Tovya]

Definitely the COD4 moment when the nuclear warhead goes off...

The game lets you crawl out, thinking that you might actually survive, and then the radiation finally kills you.