Grim Fandango, without a doubt. There's nothing easy about saying goodbye to Glottis
It's the "we'll always have Paris" moment of adventure games. Only with a skeleton and a demon.
Also, without a doubt, the scene in Still Life between Mark Ackerman and Gus MacPherson in the sewers of Prague. Can't say much more without spoiling the game
It left me in shock and sadness.