Well,
Infinite Space came to mind, as the only game I've played that could both be called story-based and contains character deaths. Ha. So, between
the scene's music, the fact one was working with him in the past and how soul-crushingly easy his fleet is to destroy at this point, I've found the death of Vilchjo Valso to be the most poignant.
Spoiler:
"Take a good look at me: a portrait of a broken man whose nation was conquered.
You've got to gather a lot more power if you want to avoid the same fate.
Fail, and the Large Magellanic Cloud will fall just as easily as the SMC did."