Medal of Honor: Allied Assault. Sweet game that didn't even HAVE an ending. I felt SO ripped off.
I also have to agree with the FF7 Aeris scene, as well as Crono's death.
I also remember back in day when my characters in Final Fastasy 1 (NES) "grew up". I felt like I'd really accomplished something, that I'd somehow evolved with my characters