[aRaven]

the ending of Prince of Persia - Sands of Time....

bittersweet, after all the things the prince and Farah have gone through, Farah cannot "remember" anything. Makes me so sad...I guess I am the romantic type 

[Grizzly]

My first encounter with an 'controller' in Stalker: Shadow of Chernobyl... was terrifying.

[kir2yar]

Deus Ex - the best stealth action/3d shooter/RPG (depend on your gaming style)!
I remember the moment when Paul is killed.
Although it turned out that it can be saved.

FF7 - Aeris

The Longest Journey - Nice qest. I almost fell in love with the main character in the game.
DreamFall - same.

+1 to unreal1 (My first game against opponents with a normal AI)
UT2004 - when I run UnrealEditor and look on UnrealScript.

Blood (1, 2) Dual Thomson!
DukeNukem3D. Yeah, piece of cake!
Heretic

[Roanoke]

Deus Ex - the best stealth action/3d shooter/RPG (depend on your gaming style)!
I remember the moment when Paul is killed.
Although it turned out that it can be saved.

There's probably an old thread/post knocking about here when I saw him alive and figured it to be a glitch...

[Locutus of Borg]

In buried in time when Arthur says he's the virus

 

[kir2yar]

In order to save that Paul should stay with him at the hotel, where he was sitting on a chair and complained of an irresistible desire to die. It is possible that in the next mission will have to save him from jail. I do not remember exactly.

[Kosh]

Freespace 1 - Pretty much everything after and including when the Galatea blew up.

Freespace 2 - The whole third act when what should have been a smooth ride turns into a desperate bttle to survive.

Freespace 2 BP AoA - the very last mission what you find out what's really going on.

Freespace 2 BP WiH - lots of missions in there, especially the last one. I've actually never seen a campaign from any game that sent you on as much of an emotional roller coaster as this. That's just good writing there.

Deus Ex - When you find out how much of an evil bastard Page really is. Not just that, the history of the Illuminati, their sins, and how they are trying to use you. Scum, all of them.

Starcraft 1 - Mengsk's betrayal. But also the last few missions of the Protoss campaign.

EDIT: Forgot one: Master of Orion 1 - there was one game in particular that I played a few years ago that really stood out amoungst all the others. I was playing the Klackons on hard, and right next to me was the Silicoids. I was smaller but more advanced, they were bigger but less advanced. Ultimately I got locked into a huge war with them that lasted most of the game, with me being on the defensive for almost all of it. The battles were epic. From the Siege of Ukko, the Assault on Nitzer to the Battle of Kholdon plus many more along the way,

It is possible that in the next mission will have to save him from jail. I do not remember exactly.

You won't have to, he'll be in the same place and you just have to talk to him. He'll get away unseen on his own.

[Androgeos Exeunt]

Pyro-GX burning up in the intro of Descent 3. And that was when I didn't know anything about it either.

[Commander Zane]

My most enraging moment in Descent was having to use the **** GL because the GX was slagged.

[StarSlayer]

I took the Orange Box for another spin recently.  To be honest Half Life 2 and its follow on Episodes are pretty amazing.  The atmosphere of fear and oppression when you first arrive at City 17 is palpable.  You literally feel the weight of this giant military/enforcement complex on your shoulders.  And then there's Alyx Vance, possibly the only game character thats ever really made me think of as a living breathing person.  This isn't to say I haven't invested in other NPCs, Bioware games and the PoP series spring to mind; but the folks at Valve somehow managed to make her totally believable.  She reacts to things like you would expect a real person to behave, a smart, funny, capable one at that.  I think her character's humanity and emotions really allows the player to have a layer of context on the story that would be sorely missed otherwise.

And of course one can not forget the late and beloved companion cube. (I should mention the early previews of the Portal 2 CoOp looks to have a large potential for being invested in your robot avatars' adventures, which'll be interesting since it CoOp.  I dunno lure you're significant other into playing )

[Nuclear1]

Homeworld --

Spoiler:

Awe from the Mothership launching, sadness from Kharak burning, hopelessness in the events leading up to the last mission, indescribable emotions over the ending cutscene (mostly vindication, a sense that "so many lives lost" weren't so in vain, and happiness verging on tears that the people who had watched billions of their loved ones be burned alive had reclaimed such a beautiful world).

So many different FS moments I can't begin to list.

The nuking in CoD4, especially the sequence immediately following it.

I'm going to second the Beast from Cataclysm.  The first few missions involving the Beast were just plain nerve wracking.

[NGTM-1R]

I'm going to second the Beast from Cataclysm.  The first few missions involving the Beast were just plain nerve wracking.

The mission protecting the Republican Taiidan convoy ships was nightmarish the first time I did it. I don't know why; every subsequent attempt has been perfect. But the first time...

I had to pull back my fighter screen to defend the Kuun-lan and there just wasn't anything I could do for the convoy. I kept sending the Acolytes back out to try and save the convoy but I was losing my fighters since they were so far from repair and rearm. I couldn't do both, and I lost a convoy ship before I pulled back the fighters for the last time.

And of course, they scream the most nightmarish scream of pain and anguish you can imagine when the Beast infects them...

I was defending the Kuun-lan, almost numb. I know there's nothing more I can do for the convoy, my fighters are too few and there's nothing in the mission to support building more. And the convoy ships keep screaming when they're infected, and there's nothing I can do. After the third I had to turn the sound off and just pretend the Republican Taidanii weren't there.

[Nuclear1]

Ever just try moving the Kunn-Lann over by the convoy's path?  Rearm and repair gets a lot closer, and replacements come a lot quicker too.

[Hades]

What, you didn't have a Taiidan Cruiser (or two) like I did? They REALLY help, especially with the energy gun upgrade for all ships. :<

[Nuclear1]

I dunno, Tai cruiser tended to make things worse...it's great, but it's a huge liability with those Beast Cruise Missiles around.

[Vidmaster]

the ending of Prince of Persia - Sands of Time....
bittersweet, after all the things the prince and Farah have gone through, Farah cannot "remember" anything. Makes me so sad...I guess I am the romantic type 

oh yeah, that was incredibly awesome.

[0rph3u5]

Mass Effect 1&2 have a lot of emotinal moments - dont need to repeat all of

SpellForce 2 - Shadow Wars

Spoiler:

when Shadowsong sings in memory of her dead father and your dead clan after you arrived in the Orc Capital

SpellForce 2 - Dragon Storm

Spoiler:

the scene of you dying in the end, your party gathering as you do your final breath and even Yasha showing some affection towards you

StarCraft 2 - Wings Of Libery

Spoiler:

the Last Stand of the Protoss against the Hybrids

Warhammer 40k - Dawn Of War

Spoiler:

Isador's descent into Chaos and the final stand off with Gabriel in Mission 10 - (and I'm actually a Chaos fan)

Homeworld

Spoiler:

Seeing Kharak burning in Mission 3

Legacy Of Kain: Defiance

Spoiler:

the outro - Kain feeling "the first bitter taste of that terrible illusion called hope"

FreeSpace 2:

BP: WiH

Spoiler:

Ken, Aristeia, One Perfect Moment, Delenda Est, Sunglare

ST:R

Spoiler:

Abandon Hope

and now one you might not know
The Shard Of Infinity

Spoiler:

As Stars Fall (SoI1-04), War Within A Breath (SoI1-05), The Illusion Of Choice (SoI3-01), Requiem For A Galaxy (SoI3-14)

[MR_T3D]

STALKER: Call of Pripypat: when I ventured into lab X8
the main emotion was that of fear.
what is this place?
WHAT THE **** SEES ME!??!
WHY AM I TAKING DAMAGE
auto-shotgun owns snorgs, hehehe
what is that sound?
a child weeping?
must be a trap, but...
...****ING BURER!
**** it, I have a document, I'm out of here

[Hades]

I dunno, Tai cruiser tended to make things worse...it's great, but it's a huge liability with those Beast Cruise Missiles around.

Yeah it's best if you have some fighters guarding it, but it still needs a good bit of micro managing. Since it's so slow, I had a bunch of fighters in two more groups at the middle and other end, worked like a charm.

[NGTM-1R]

Ever just try moving the Kunn-Lann over by the convoy's path?  Rearm and repair gets a lot closer, and replacements come a lot quicker too.

It was too slow to get there by the time I remembered it moved; I was following on directly from a playthrough of HW1 and that tends to cause a bit of a mental block about moving the Mothership. I think that was why the last convoy ship got through and I didn't fail the mission, though completely by accident.

I did actually have some multibeam frigates and they were a tremendous help defending the Kuun-lan. Didn't lose any either.