So many, but here's a selection:

Shadow of the Colossus: Everything. It's just that good.

Lunar:Silver Star Story/Lunar:Eternal Blue: Both games have several memorable moments, but one unassuming one was in Eternal Blue, when Nall reminisces about his friends and their quirks; for me, it just suddenly hit home that all the characters from the first game really were dead and gone.

Legend of Dragoon: Most of the cutscenes, but especially Lavitz's death (that one really came unexpected), the flashback to the previous Dragoons' last battle and the ending sequence.

Parasite Eve: The opening cinematics, with Eve signing while everyone around her burned will forever stay in my mind.

Enslaved:Odyssey to the West: The whole ending sequence was a gut punch.

Uncharted: That moment when it switched from action-adventure to survival horror.


And for completely different reasons:
X-Com: Hearing a sectopod walking somewhere in the dark; getting your high-level Assault mind-controlled, and the only way to reach the enemy is past them; and the first time you go into the mission investigating the fishing village, not knowing what is in store for you.