Super Metroid. The whole last section of the game is such a huge emotional roller coaster:
The dread of finding all the husks of the space pirates, evidence of the last Metroid which has looked to you as a parent it's whole life.
The terror of said Metroid (super huge and scary) almost draining you to death, stopping at the last moment and recognizing you.
The despair that nothing you can do will bring down Mother Brain, but being rescued by your Metroid, only to see it destroyed by your nemesis.
The thrill of revenge, and subsequent frantic escape! Great stuff.
Then there was Holly Summers in No More Heroes. I didn't cry, but I got emotionally "pulled in" watching this scene between her and Travis. In the end I was in a funk for like a week.