One of the most powerful for me is Nanaki finding out the truth about Seto in FFVII. It gets me every time. In contrast to Aeris, where I QQ'd the first time, but that was it - I've seen the Seto scene over 20 times and still cry.
In Dawn of War II (SPOILER)When Dreadnought Thule shows up and kicks a retarded amount of ass. Actually pretty much everything Thule does, both before and after dying.
The ending of FFX is fairly powerful. I did tear up.
In Mass Effect, the entirity of Virmire is just one long, long level of serious win, from being totally pumped up because it is badass, plot reveals, and (SPOILER)having to choose between Ashley and Kaiden. No third option exists. One of them dies, no exceptions. And BioWare did such a good job at making genuine characters who feel like real people that it hurts either way.
In Call of Duty 4, on two occasions. First, the intro: You're in control of a dude, only not so much, because he's being led to his execution. You can't do anything about it, just look around the place. Secondly, and even more harrowingly, is when the game subverts pretty much every FPS trope going and (SPOILER)not only do you fail to stop the nuke going off, you're caught on the edge of the blast radius - and could have been safe if you hadn't stopped to save a downed pilot - and you die. You're in first person control of this guy and all you can draw is crawl around the charred ruins until he finally gives up from pain and exhaustion. No weapons, no enemies, nothing except about a minute of suffering. That's just not how an FPS game is supposed to be, and it's all the more powerful for it.
Shadow of the Colossus. The whole thing. I've rarely felt so conflicted while playing a game, but this is certainly one of the only ones I've felt I might not be able to carry on with, and considered that a good thing. If I hadn't had the heart to finish SotC I would have considered the money even better spent. I want those emotions in games, I want things that challenge and conflict, it's the same as the CoD4 stuff I was talking about. Seeing those mighty, often very beautiful and noble creatures struck down... not knowing if I could trust the dude I was bargaining with in the process, etc. That, more than anything, more than aesthetics or atmosphere or gameplay, is what makes SotC such an incredibly important game.