I see a lot of games being thrown out there, but no specific moments. So, I'm gonna buck the trend and speak to specific moments.
CoD4: All Ghillied Up
You're following McMillan, and he says stay close and move when I move. Then you proceed to run/crawl through an enemy encampment. My heart was thumping, my palms were sweaty, and I couldn't wipe the grin off my face. Amazing set piece.
HL2: Sandtraps
You're given a dune buggy and proceed down the road. Many things happen along the way, and all are memorable. Strangely enough, however, what sticks out the most for me is seeing them take the buggy away. It's just a prop really, but by this point it has gotten you through so many situations that anger can't help but bubble up. It's strange how we can form emotional attachments to inanimate objects. Which segues nicely to...
Portal: TestChamber 17
The source of the ubiquitous companion cube. Much has been made of the psychology behind the level's climax, and it's higher meanings. I won't touch on that. All I know is that I had an actual pang of guilt when I did what was required.
True emotional response occured in all these situations, and these moments are the template that I compare future games to.