Half-Life:
--Walk into a room. Hmm, nice and quiet in here. Then you hear the black ops running around. Freaks me right out.
--If you get killed by an alien grunt and just sit there for a few seconds, it'll walk up and start eating your corpse.
--Damn freaky administrator. Always showing up. Being a freak.
--The 3 big... knife heads. in the missile silo.
The last 15 minutes or so of Metroid II took me through an emotional rollercoaster. I felt a great sense of accomplishment beating that.
The ending to Secret of Mana. Especially significant because I always played as the sprite.
Bionic Commando: Take that, ya lousy Hitler knockoff.
Someone's already mentioned GTA:VC, that was a great ending.
Phantasy Star IV had a lot of great moments. Alys is also my favorate character, like, ever.
Shining Force. The ending was pretty good. I don't think I've ever seen a better credits sequence either.
Blood: A lot like Max Payne, sort of. You've already lost, but there's something to be said for revenge.
Theif: From when you get your eye taken out to the end. Most of the walled city was also damn scary.
The opening of Descent 3. They pull you in, cut you out of the drifting ship, and just dump it in the sun. They can't do that! That's my ship damnit!
Doom II. I forget what episode. The earth is evacuated except for you and all you can do is sit there and wait to die.
Then they throw in some more lines of text that totally ruin it.
Also, theres not a lot of videogame bosses I find scary, but Cyberdemons are one of them.
Wow, that's a lot. Well, I'm a geek.