Tough one, but I'll try.

Planescape Torment: almost all of it.
I couldn't even play anything for a fortnight or so after I finished it.

The Witcher:
Geralt defending Abigail - that was a badass scene.

Definitely more than that, but can\t recall at the moment.

At the end of Diablo, I shed a tear when I realised there were no more monsters to kill. :(

Just kidding, for me it was the Bronze Sphere moment at the end of PS:T.

The last minutes of Gone Home, when I realized what was up in the attic and thought there might be a chance I could get there in time to help. I ran through the house, heart in my throat.

Dark Souls - the fate of Oolacile and the endings (all "three" of them; one of the devs said that if you don't finish the game but quit playing it altogether, it's supposed to represent your character going hollow - a third ending, of sorts).
Deus Ex: Human Revolution - the final confrontation with Hugh Darrow. "Actually, Mr. Jensen, I prefer to think of myself as Daedalus, watching helplessly as his child crashes into the sea..."
Vampire the Masquerade: Bloodlines - the fate of your ghoul if you don't release her from the blood bond.
Max Payne 1&2 endings - all of them.
Dishonored - high chaos ending (although I admit I beat the game with low chaos first so it might be the contrast that struck me).
Pathologic - the fate of Eva (I played as the Bachelor), the ending.
Planescape: Torment - lots of things, actually.

While it certainly didn't have maximum impact...

I have just finished Two Worlds 2 - Pirates of the Flying Fortress and I have to say, the fate of the cartographer touched me quite a bit.

Now that you mention it. I remember Diablo - the ending was really emotional for me. I forced myself to continue playing the game beside it being so boring, expecting that anytime the story of the game should start - and then the game was finished!!! Yeah, at that time I couldn't imagine, that a RPG could have no story at all. So I was really astonished. (and this is no kidding)

I beat the game three times and each time I released her. I'm SO stupid! It goes without saying that I never got the best armor set.

Also one of the most emotionally powerful moments in gaming for me was in Kane & Lynch: Dead Men. The "you should have let me talk to them" scene. It was insane. I don't really remember any other game where I felt so connected to a protagonist in a state of complete blind rage. It may not have been a gaming milestone like the Max Payne prologue but it managed to pull off the same intensity many years later which - to me - makes it just as special.

In Morrowind there were many moments.

1) I was talking with the Ashlanders, and told to go to the Cavern of the Incarnate - where, you learn, the bodies of all the previous prophesied Incarnates are buried. All through the game there are hints that the PC isn't the first Incarnate, and if you don't succeed you won't be the last. It all comes to head here, where I spoke with the ghosts of those who'd gone before me and failed at what I was trying to do. It was very powerful.

2) I was sneaking through the temple prison in Vivec looking for a specific person. The directions were a bit vague, like all Morrowind directions, and I wound up being trapped in the library for a bit to avoid the guards. So I read a book while waiting, and suddenly realized why this book was locked up in the temple! I'd managed to land on one of the censored books. It seemed such a true-to-life moment. Where else who a secretive shamanistic cabal hide their banned books? In their secret banned books library! :D

3) Talking with Vivec at the end of Morrowind was an amazing moment. Vivec had been a distant antagonist all throughout the game, but at the end of it he and the PC had more in common than not. My strong desire to punch him was not diminished one iota, but I came away with a new respect for the balancing act he'd played, and for how deep the lore of Morrowind really was.

TL;DR Get Elder Scrolls Morrowind and the MW Graphics Overhaul. Prepare to get sucked into an awesomely alien world.

The Overlord games are also great at believably over the top characters. Gnarrl and the minions are awesome. The Wii Overlord game had a great story with hilarious antagonists. I can still remember fighting the PC's brother who faffed off to the elves. There was one particular cut scene where the brother is trying to hard to be mystical and elvish, and the elves all kind of facepalm. The sister was a great genuine antagonist - some of her lines were genuinely scary in a 'I can't believe she said that' way.

Seconded. Especially the burial cave and the talk with Vivec. Although I already had quite some respect for him from what I had read in all the books about the Dunmer history. I also felt quite some sympathy towards the last Dwemer... And finding Sotha Sil in tribunal was kind of a sad moment...

last of us, many many moments throughout that stellar game


mass effect ending

myst, choosing the wrong person to save

riven, the horror at those awful sons deeds

to name a few. these moments are why I keep buying games in the hope that the next title will have those goose pimple inducing moments of clarity. they are gems of story telling greatness and sadly hard to find

It did cross my mind that I should probably let her go, but I saw no reason to do so and it had nothing to do with the armor. I mean, the two ghouls you encounter (Mercurio and the Bertram's one) get to live longer and better than your average human, enjoy the perks that come from having a connection to Kindred and are overall more than happy with their lives, so I thought it would be a win-win situation for both my character and Heather.
Well, in the unmodded game it doesn't work out so well, but how was I supposed to know?

Ah, rage. Yes, that is an emotion as well. You know, it reminds me of a certain mission in Hitman Contracts where the task was twofold: the client wanted his kidnapped daughter rescued and those he deemed guilty dead. Nothing special really, rescue missions were featured in previous Hitman games (mostly involving a certain Agent Smith). I was going for the Silent Assassin rank (less than 2 shots fired, no alarms raised, less than 2 people(who are not targets obviously) killed). The infiltration phase went smoothly and I set finding the girl as a priority. Well, when I did find her... most of her anyway, Silent Assassin was out of the question. Every single person on-site was strangled/poisoned/shot/stabbed/whatever - I beat the rest of the mission in a state of cold rage.

Oh man, yeah, I totally forgot how powerful that scene was the first time. I was seriously disturbed after it. You open those doors, the lighting changes and you hear this "nice" music. The insane thing was, while I was still standing there shocked by the discovery that guy suddenly entered the room and ran at me swinging a cleaver. Without any hesitation I drew my SMG and emptied a whole mag into this sick bastard.

I actually beat like half of the game that same day but I couldn't stop thinking about the Meat King mission. It's some really intense stuff. It kept sending shivers down my spine all day long.