+1 for Machinarium. All those memories that pop up when you're not moving the character. That got me, it's a beautiful and touching way of making you care for your character.
The scene that hit me the most is at the end of Ico:The shot where Yorda put Ico in the small boat, and you see her from the boat's point of view, all black.
Also the end of Silent Hill 2 hit me hard, although it was becoming pretty obvious:First when you realise the last "level" is the hotel where you spent your honeymoon or whatever. And then, when you enter the room you used to be in, it's shockingly all clean and calm in contrast to everything else that is SilentHill-esque. And then you watch the video and realize what you've done.
The end of MGS 3, although I've always found that the stories in MGS games were overly complicated badly writen pseudo polical-warish bullshit, worked surprisingly well on me. That last scene where Snake realizes he's been a tool all along, it works very well, you can really feel how heavily impacted he is, and that's a decisive turn for the rest of his life.
Shadow of the Colossus:Agro "dies". Holy shit I was like "damn you Ueda I knew it you mean man!!". But even more powerful is when he's coming back, limping. It's all just so perfect at this moment. The ending of this game, although predictable is incredible. You don't really know who's bad and who's not. You've been fooled by Dormin (not really actually he warned you), but at the same time he was true to his word and she lives again. This game is fucking awesome. I'm plugging my PS3 back right now! (damn you PS3).
Actually we should totally do a thread on the exact opposite! Scenes in games that tried to be emotionnal but fail miseraly (bad writing/directing/dialog, etc). But there would be just too many