Eternal Darkness: Sanity's Requiem. You play a whole slew of characters throughout the game, and the story was well written, they weren't generic apart from the main protagonist, and seeing them die, go mad etc one after the other was quite disturbing.
Chibi-Robo! Probably a bit of an unusual one, as you play a little robot in a cutesy world, but you live in the household of a dysfunctional family. The father is unemployed, the mother is unhappy, and the kid is a bit fucked up. As you play, you carry out household tasks and generally help out, leading to less conflict and more happiness. Put a real smile on my face.

I'm bumping this thread to thank everyone who mentioned The Longest Journey there.
Wasn't the first time I had read or heard about it, but this thread was what prompted me to actually try it.
And.. well, i wish I had done it sooner! I don't think I've ever smiled as much in a game as when hearing or reading her. Definitely a gem of a game...
Thanks again to those who wrote about it (-:

well since the thread is alive again;;;
Must say that there are few games that really make a big emotional impact but;;
ICO and Shadow of the Colossus deseve a strong mention - both have a very emotional feel as you play the game and the ending for both is very moving. What is even more impressive is the almost total lack of speach in the games - its all done through sight and experience - a very different way of story telling.
Homeworld - one of the best stories for an RTS game and also backed up with great mood music. A great shame that the sequal lost the magic that the first had.

Well, I just downloaded the Van Buren tech demo last night and that had me a little teary-eyed considering just how close we were to having Fallout 3 made by BIS/Interplay. I had to turn it off because I thought it would be best to let the dead rest.

Cried when I finished Link's Awakening.

A few of my favorite moments:
Silent Hill - Walking through the sanitarium (I think? The school flips over, filled with chainlink and gore), the phone rings - still gives me a chill.
Also the first game I thought effectively used vibration (maybe that was 2...)
I was playing this again at a friend's house - he refuses to play that game with surround sound and the lights off at night... anyway, in the middle of may, 1am or so, the sirens go off outside the apartment... we look out the window - everything is dusted with snow, with a few parking lot lights shining in the darkness, and that great hardware limitation 'fog' effect... the look on my friend's face - priceless :)
To this day I hate early warning sirens going off.
Halo - First encountering the Flood. I didn't play it until a few months after release, and NO ONE had said a thing about it - none of my friends, and as far as I could tell, no message board comments. Good stuff.
My roommate and I still pick up the co-op campaign from time to time... but we both hate playing after the Flood is introduced... Annoying things.

Penumbra: Overture, when you have to incinerate the man who's been helping you through this mess because he wants to die.
GTA IV, when you're returning to Roman's taxi company, business as usual, when you see it up in flames. I felt so guilty when that happened.
Shadow of the Colossus, pretty much every time you killed one of the giants, and the ending.
But the most emotional part of a game would have to be the end of The Darkness. Them meeting one last time and talking about how they could never see each other after this made me so sad. That game told its' love story better than any other game I've played.

Any screaming death in Alone in the dark, such as when you read the Necronomicon. Also, several times when a zombie/monster appears. The game wasn't fast paced: both the hero and the enemies were considerably slow. But it apparently added to the feel of having to run as fast as I could.
Also, the final battle in Black (PS2). It not only took me a whole afternoon to finish it, but put me in the situation of wielding a revolver (heavy caliber) against a missile launcher freak several times, until I finally had only one chance left and he died after missing me for centimeters. Bastards.
Ah and the sex cards in The witcher, very inspired idea, I was impressed on the first time I saw it happen.

Oblivion
The physics and such were so cool. Made me genuinly smile, which is anything but common.
Finaly Fantasy 6
When Figaro first sunk into the sand. For some reason that memory really sticks with me.
Final Fantasy 7
You know the scene. I found it depressing.
Vakyrie Profile
What *doesn't* have an emotional impact in this game? Most significant moment for me being the one where "A Chamber of the Heart' is played.
Radiata Stories
Being caught in a conflict with no good side or ideal choices. Having to wage genocide against your former friends on whichever side you didn't pick, especially if you betray humanity and pick the fairy side.
Shadow of the Colossus
I should not have to explain this. Seriously.
I know there must be others, but those are the most notable I can think of offhand.

A small bump after all these years :)

The kotor 1 revelation was completely unexpected for me and my mind was blown.
For some reason talking to 'O' in planescape torment really stuck with me
Their were many emotional moments for me in kotor 2.
Total annihilation when i built my first krogoth.

Epic revival..!