(I'm surprised no one has said anything about Shadow of the Colossus yet... )
But I'd have to say Silent Hill 2 when you speak to Angela for the last time on the flaming staircase.
It's not like a huge sad ordeal or an epic end or something like that. It's a moment you realize this character's past trauma can't be fixed in the end (it's probably even too late for them) and the protagonist James can't do anything.
It's a conversation between two broken people, the indifference of caring about someone, and it just leaves you with an emptiness feeling.
"It's hot as hell in here."
"You see it too? For me... it's always like this."