I absolutly hate the forced death of the main character. Expecially if the game wants that character to be "you".
In over 20 years of playing RPGs (virtual or otherwise), i have never seen a game do the "You, Hero! Go and sacrifice yourself for X" trope done right in ANY RPG. If the protagonist is supposed to "be" the player, then the player will inevitably try to find ways to bypass or avoid said character death and if those arent present in-game, then said story will feel lame (at least i think like that).
To me it only really works for stories where the protagonist is his own character and the player has no close connection to it.
And ok, i lied about never seeing good motivation for character death, i think if in the Baldur's Gate saga, if at some point it was asked for the player to sacrifice himself for Viconia... i would do it