I agree, Twilight Princess was pretty emotional at a few points.
Kingdom Hearts and FFX also tugged at the ol' heartstrings. The whole Sora/Kairi thing was pretty sweet. The whole game had a strange, sweet, twisted, dream-like quality to it. I don't know if any game's love story, and story in general, drug me in quite the same way that FFX's did. Beautiful game.
Metal Gear Solid 3's ending was killer and made me misty-eyed. I was discussing it with my brother just the other day.
The game Ico was itself quite emotional for me, though I don't think it ever had me approaching tears.
Out of this World had an emotional ending as well. Awesome stuff.
I agree with you, (spoilers) I also felt really sad when In Shadow of the colossus the horse dies, and it felt kinda weird when Wander turns into a baby.(in a good way)
*Shadow of the Colossus Spoilers*
I agree, when Agro sacrifices himself to get you to the last colossus definitely elicited more emotion than anything in recent memory. He does end up surviving though if you watch the whole ending, though he is injured.