Eternal Sonata at the end when Chopin turned against you and attacked you :(
The first time playing Final Fantasy 7 when Aeris died :(
Rogue Galaxy - The feeling of losing Kisala at the end to a throne of the galaxy :( The feeling of not seeing her again.
Portal - The sheer enjoyment of playing that game
Original War - The great feeling of controling your base with you chracters that had a personality
KKND - The awsome mutants was funny as hell :D

Baldur's Gate II ToB
This choice in the very ending - to be a god, or to remain mortal and live together with women I love...

Penumbra: Overture scares the living daylights out of me. I went to play it a few days ago, and I had to turn it off after about half an hour. I'll finish it eventually, but it's going to take me a long time to do so.
The big twist in BioShock was also pretty powerful. As I listened to what he had to say, I suddenly didn't want to kill Andrew Ryan, but I could do nothing but watch as I did.
And don't forget Riven. The end, where you just fall into the Star Fissure, is very powerful. You have just ended the life of that world. Indeed, the game ends with you watching the Fissure expand, consuming the entirety of the Age. And what happens to you?
Also Saveedro's story in Myst 3: Exile and Achenar's story is Myst 4: Revelation.

All too familiar. Or how about "bad command or file name"?
Nope, I'm pretty sure we were all fairly attracted to that sexy little sprite. :-D Truthfully though, she was a great character.

Blade Runner : getting back home and finding a meaningful quote of William Blake on my answering machine.. the atmosphere of that game is great, and that was one of its peaking moments for me.
Deus Ex 1: on the second time I played through it, finding out that I could actually save Paul at the hotel and keep him alive through the rest of the game. Had no idea that I could actually disobey his command and stay to prevent his sacrifice.
Arcanum : at the beginning of the game, in Shrouded Hills, being asked for money by a hilarious would-be thief and then convincing him that I was from Tarant's thief circle and that he was the one who should pay me.
Baldur's Gate 2 : getting Viconia stolen by Bodhi -- that actually hurt
Myst IV: Revelation: On Haven, when I finally solved that Mandrees + plant vs Camoudile, getting that video sequence, having Mandrees follow you around and play near you. That was just incredibly rewarding (I spent a lot of time finding the solution ^^).

I totally agree. I felt the same emotion when I played it all of the times after that. Her death didn't hit me as hard because I knew it was coming, but still, the moments leading up to that were a bit tough to get through.
I hope GOG offers FFVII and VIII for purchase, I don't think my copies will work in Vista.

Just about any character interaction in Planescape: Torment. I hope that GOG manages to obtain distribution for it. I own the original and it still works but I'd pay again just to let GOG know they rock.
Redemption's a powerful thing. And it gets weird dealing with redemption for acts which one doesn't and can't remember .
For those who haven't played before, here's a hint: INT WIS and CHA will count for much more than STR DEX and CON.

American McGee's Alice- Just for the strange mix of shrieking anger at terrible mechanics and awe at superlative environment and mood.
Vampire: Bloodlines- Rarely do I feel sad that its over. The mood and ambience is so enveloping, I didn't want to leave LA at night.
Although it's still quite new, I find The Witcher to be exceptionally powerful in many places. Just as an example, the desolate lonliness of Kaer Mohren; you know it was one a vibrant and powerful stronghold- now reduced to a crumbling husk, much like the witcher profession itself. There is a meloncholy to the place, just being there is somehow sad.

Torment took it to extremes, though. It was a game-based story more than a story-based game, and if you didn't actually focus on INT/WIS/CHA the game practically punished you by making the story less interesting. Who would want to miss out on all that sweet, sweet story?
On the whole, I'd say Baldur's Gate 1 + 2 had a better balance between story and combat (more so if you factor in the tidal wave of mods available to tweak both). Torment was awesome, but it never gave me a "damn, how am I going to get through this one" moment like an actually challenging game would. Instead it was a succession of "damn, that's awesome" moments.
The extreme focus on story is why half the world considers Torment the best RPG ever, and the other half complains that they wanted to play a game, not read a mildly interactive book. The sheer amount of text in the game is staggering, and the game hands out XP practically as a reward for reading it all. Conversely, combat rarely becomes challenging, and the few times it does it just becomes frustrating (so it's a good thing that almost all combat can be avoided through stealth or wits).
Since I have Dak'kon as my avatar, you can guess that I'm actually one of the people who considers it the best RPG ever, even if it didn't have the best gameplay ever. The purpose of a game is to entertain, no matter how it does it, and I love reading a good story. :-)

This is absolutely the best description of Torment I've ever red...
I have to add Witcher to my list. Saving Abigail, one possible romance, and so on... It's awesome

The fist time I played Descent online ,I was in awe!

I remember trying World of Warcraft, and entering the throne room in old lordaeron where Arthas had killed his father...I actually waited and had a short moment of silence.
Also, most definitly TLJ and Dreamfall...epic game storytelling.
Finally, forcing myself to play evil in Kotor so as to see the other ending...I have rarely felt so utterly heartless.

Small moment really - text "adventure" game from the '80s: Hitchhiker's Guide to the Galaxy - getting that darned BabelFish out of the vending machine. That was a leap out of the chair and dance around the room moment.

Just kinda scanned the last page and was surprised to see that no one mentioned Max Payne. That game was pretty crazy, and did a decent amount of the 'ole emotional rollercoaster. I was definitely a victim of FF7's Aeris scene. Dang.. there's a few games I'd really love to give mention here because there's some that have really had serious emotional impact on me, but I just can't think of which ones they were right now.

Most emotional I ever got playing a game would have to be Metal Gear Solid if you got the "Bandanna" ending rather than "Stealth Gear" ending. Don't want to spoil it for those who STILL haven't played it.
Also in FFVIII the whole scene in space gets me every time.
System Shock 2 when you meet SHODAN was pretty shocking. BioShock also, but to a much lesser extent. Still kinda clever.
Loading up HL2 for the first time after waiting to play it for so many years. But that was mostly just the anticipation on my part. I played it the moment it was released on Steam.
The KOTOR twist caught me off guard (much like Fight Club did).
The ending to MGS 3 is pretty sad. Especially when it makes you pull the trigger.
Dark Messiah of Might and Magic when you discover what you are.
The ending to Vagrant Story (probably my favorite video-game story other than Planescape).
Suikoden III when you discover the Flame Champion.
Legend of Dragoon - the ending to the first Disc.
Summoner after you destroy the rings (probably one of the most under-rated games on the PS2).
Castlevania SOTN after learning you've only played half the game.
Resident Evil 2 - The Second Story (or whatever it's called - the OTHER side of the game).
Fear Effect was pretty fucked up as far as I remember, but I don't really remember why. Ha ha.
And last but not least...
Silent Hill - the ONLY game I CANNOT play by my self after midnight. Fucking school kids give me the creeps.