Final Fantasy VI: Kefka f'ed up the entire world.

Final Fantasy IX: Really liked the existential themes. Vivi's development really touched me.

Persona 3: MC's death at the end.

Persona 4 Golden: Such an enjoyable game and I didn't want it to end. Really liked Adachi's social link.

Shadow of the Colossus: What have I done...?

More recently, Alan Wake.

Metroid Prime 2: echoes

Shadow of the colossus i felt really sad at killing those beautiful creatures also mgs 3 climbing the ladder and music kicks in.

Xenoblade Chronicles. Bravely Default. Fire Emblem Awakening are just some of them

Tetris

Mass Effect 3

Not the overarching story, but the sub stories and the character relationships built over the past games coming to their respective ends

Silent hill 2 and shattered memories are pretty powerful.

Oh and I've played through TLOU seven times and that ending still gets me every time.

The Mass Effect franchise definitely. Oh and The Last of Us :)

TLOU easily.