There were a few games and moments that got to me in one way or another.
In Silent Hill 2, I actually got really, really upset during the scene where Angela walks off into the burning building. I just felt so much pity for her; she didn't deserve what had happened to her, but she didn't have anyone to lean on, and she let the things her father had done to her destroy her self worth. It was an interesting moment because I realised she was basically playing out her own version of James's journey into Silent Hill, but she wasn't strong enough and she was lost. It was just an absolutely brilliant bit of storytelling and empathy for a character who didn't even get that much screen time.
Conversely, the ending of Earthbound was probably the best ending I'd ever encountered in a game. Still is, purely for sake of closure. I loved how you got to travel through all the different cities you'd been to, seeing everyone you'd met, the way your actions had changed things. I remember I finally got to the library in Onett and returned the map, and the librarian said something to the effect of, "Wow, I can tell this map has traveled very far and seen a lot of things... but it's home safe to stay now." and I kind of just gave a happy, contented sigh. I wish more games had endings like that... it was just the perfect way of wrapping everything up, of making you feel like your adventure really had come to a close in the best way possible. Sure is a far cry from today's 30 second cutscene and ten minute credit roll.