Out of some of the games I've played (spoilers):
Final Fantasy X ending- That left me in absolute tears. Damn it, they should have left Sin alive if it meant that would happen.
Metal Gear Solid 1, 2, 3, 4 & Portable Ops- MGS had Meryl's death scene, and since I am terrible at the torture scene I am always unfortunate to get it. And Sniper Wolf's death, that was sad.
MGS2 had Olga, Fortune and E.E's deaths. As if I didn't feel bad enough after Otacon and E.E's moment, up comes Olga and Fortune.
MGS4 left me feeling very remorseful over the end of Snakes story, "Here's To You" really helped make the mood for me.
Portable Ops had Elisa/Ursula's death and "Calling To the Night". I find Elisa's death and her vision heartwrenching considering what will happen later in the timeline, and "Calling to the Night" leaves me all teary-eyed. It always makes me think about all the soldiers that fought in wars. I always play it on Remembrance Day, perfect time to play it.
MGS3 on the other hand left me outright crying for a whole ten minutes. EVA's speech about The Boss was just heart wrenching for me. "Way To Fall" made me feel even sadder because it fits it so perfectly with The Boss and Big Boss.
Half Life 2: Episode 2- Eli, why did it have to be you?

Max Payne- The whole back story. Poor, poor Max. Straight away I have to feel sorry for him. Not to mention when you find out that his close friend betrayed him.
Oddly enough though it seems that compared to everyone else, Red Dead Redemption's ending didn't make me sad. Instead it made me very,
VERY angry. My Marston was a good Marston and yet that pudgy agent goes and does
that!? It was so satisfying when Jack blew his brains out.