The ending to Kingdom Hearts 1 and Ace Combat 4 for the PS2 (I think it was Ace Combat 4, I know it was one of them) had me close. And the ending to Ocarina of Time was another one that got me.
More recently, the only one I can think of is near the end of Dead Space 2.
I don't know why people tell me to get a life. I'm a gamer, I have lots of lives!