ertertwert - agreed r.e. FFVIII with the scene in space... But for me the most powerful scene in that game was the bit when the gang get the whole "Your mission... Is to save the planet..." I remember screaming out "OMG!!! THIS IS FUCKING WICKED SHIT MAN!!!" My best friend at that time's brother nearly leapt out of the lounge when i screamed. That still makes me lol even now.
FFVII was almost one continuous "Wow..." scene after another - loved how they showed Sephiroth in all his awesome power so early in the game when Cloud was a "noob" - the sheer difference and scale of the attacks was simply incredible.
God there are so many awesome games out there. What I would give an arm and leg for is a really good working version of most of the PS1 JRPGs such as Legend of Mana, (Genso)Suikoden 2, Legend of Dragoon, Chrono Cross + Trigger, Wild A.R.M.S and so on and forth... That I can play ON pc with gamepad and without stupid save game limitations - the emulators out there are so fucked in that they don't recognise that I have 400 GIGABYTES of save space instead of 8 MEGABYTES. I've lost so many save games due to updating or whatever and it's driven me to the point of insanity...
*twitches*

Two that pierce the beer-induced fog I am currently in:
(1) Deekin, in Neverwinter Nights. No other character has made me cry in a game.
(2) Operation Flashpoint: After Montignac. Doom may have given me the heebie-jeebies that the headcrab ambushes of Half-Life riffed on, but I never felt as vulnerable in a game as I did after my squad was wiped out attempting to evacuate Everon.

I can really only remember two right now:
Tales of Symphonia: When the whole party seemingly sacrifices themselves so that Lloyd can go on.
F.E.A.R: So far, this has been the only game to actually make me jump. Once you get to the office building and have all those damned cloaking ninjas or whatever they are. I was walking down a hallway with offices that had a glass wall on either side. After a few steps, I heard shattering and saw something in the shadows ahead of me. Needless to say, I was already keyed up from fighting off those ninjas, and that didn't help. I thought I was about to be swarmed by them! Fortunately, it was just a body >_<

Jesus i forgot about that one!!! I was sitting on the edge of my seat going "Noooooo...." when that happened...

the ending of Metal Gear Solid 3 when you have to put the last bullet in The Boss.... i always have to take a breath for a short moment of silence and the perfect timing in the music before i can crack the trigger.

I've had many through the years (been a PC gamer since 1989) but the most emotional moment for me in a game was in Medal of Honor:Allied Assault. After the beach assault when all hell was breaking loose, you had to travel through some hedgerows and things got quiet again. And the hedgerows emptied out into a decaying French church with an ancient cemetery behind it. Walking through there with my men, and seeing the sun rays shining through the bare tree branches and glistening off the old broken stained glass church windows, and well, I suddenly felt a catch in my throat. A game hasn't made me feel quite that way since.

I'm interested to hear all the different emotions that this thread has drawn out; it's not even just the obvious ones - grief, triumph, omg etc. - but some really subtle touches. Perhaps this subtlety is a sign of a media artform approaching true maturity in the way that film has done, despite what the traditional media would often have you believe.
There's another interesting question that affects emotional investment in gaming; open world or narrative? I'm not here to debate which is better (answer: depends on the game and the story being told), but to discuss the element of choice in the emotional element of actions.
*MINOR SPOILER ALERT*
For example, playing God of War there is a scene in which you encounter an Athenian soldier locked in a cage. At the end of the passageway is a devilish-looking mechanism that will open the way forward at the cost of this man's life. Moral dilemma, or tarted up lock & key puzzle? I would subscribe to the latter. I very much felt the guiding hand of the game designers in this section - after all, I was either to mete out an unmerciful death to this fellow soldier or stop playing the game which I'd spent my hard earned money on - and while I felt a certain degree of guilt as I pushed his cage towards the device, that was tempered by a sense of 'this is what I'm supposed to do'. By forcing me into this task, it was as if the developers took responsibility for my actions by proxy.
*END SPOILERS*
As a contrast, I recently played through GTA4, an experience which can be fully enjoyed without resorting to playing the way the developers intended you to. And certainly the game glorifies violence in many ways (but little more, honestly, than Tom & Jerry cartoons and the like), and gives you the freedom to carry out your actions as you see fit. And as a corollary to this freedom, despite the police & drug dealers in the game being nothing more than a never-ending swarm of red-shirts, I felt it a lot more when I chose to walk up in front of a car and shoot the driver in the head.
And I'm not sure I liked it. Perhaps that just goes to show that I'm not a psychopath. Or perhaps it is because I chose to take those actions. They weren't prescribed for me. They weren't the hand of the developer reaching forward and molding my actions to fit their view of the world. That was me, raising the gun and pulling the trigger.
Even if it was to blow a hole in a bunch of textures and polygons.
Teph

I loved Tales of Symphonia. Unfortunately I think I've played too many JRPGs and read too much Japanese heroic literature. The 'everyone sacrificed themselves for me' event is too much of a cliche to pique my interest anymore, and I was really disappointed that they went through the rigmarole of offing each character in turn only for them to turn up a few moments later without even a particularly good explanation about how they all escaped their imminent dooms.
Teph

The old Aliens vs Predator for PC, the graphics were above par for the time, and the sound design is what sold it for me.
Playing late at night,as the Marine, with only the computer for a light in the room, stereo headphones on, walking down dimly lit corridors with steam hissing, lights blinking, and then the HIIIIIIIISSSSSSSSSSSSSSSSSSSSSSSSSSSSS of an Alien eating your ass, used to make me jump near out of my chair, and freak me out to the point where i had to do sumthin else, LOL
Doom3 was the same as far as atmosphere goes, although no mulitplayer ruined its replay for me...

I'd have to say the ending of Lufia 2 has come the closest to making me come to tears of any video game. Even the fact that if you played the first Lufia for even 10 mins (2 is a prequel of 1) you know the what happens at the end didn't dappen the emotional impact.
I'm sure there's such moments in the first Lufia, but I was young at the time, and I've never got much farther than those 10 mins :p
Also getting Saren to kill himself near the end of Mass Effect was incredible (never got to the end of Planescape Torrment either) and the choice you had to make at Virmire made be sit the controller down and think about it for a good 10 minutes.

Oh man, I could see this place going off in a fanboy rage pretty easily.
stuff like
'oh ffviii is the best'
'no, ffvii is the best.'
'what u talk bt? ffvi lol'
Anyway, I'll keep my final fantasy dislikes and likes secret.. I don't want to start a nuclear war at the gay bar, gay bar, gay bar.
A game that gets me emotional? Teenage Mutant Ninja Turtles, arcade style.

Weclock,
I'm starting to suspect you like to stir things up... *suspicious squint*

I'm not gonna lie, I'm not quite a gentleman. I like to enrage people just because it's something I haven't seen from them, it seems interesting. Whenever there's an argument I usually try to pick the other side.

Which is why I have Virmire is after Saren offing himself, not before :D
The big thing about Virmire, I have to say, is that all too often in RPGs today, there is an obivous "right" and "wrong" choice in most matters for minimal grief, to save the most lives, to do the obvious good (or evil, if that's what your doing this playthough) decisions, thereby making most choices, at best, discomforting. Even then, Duex Ex Machina comes along and all to often makes the choices you really do fight over meaningless.
With Virmire, you have to choose who dies; there is no secret 3rd option that gets both Ashley and Kaiden out of there alive. One, or the other, must go. The Witcher, which you mentioned, embraces choices like this in a great way.