I don't mind them. I don't mind games (or even movies) try to pull on my heart strings with tragedy and all that, but it has to work.
(I should say now, spoilers for Mass Effect 3 and Fallout 3)
In Fallout 3 (allbeit no the best example), self-sacrafice was a key part of not only the main story, but the entire game. There were many quests, unmarked quests, and even random encounters where the player character had the option to help someone at their personal expense (time, resources, safety, health, money, etc). So when the big climax came along, it was natural for the game to ramp up the stakes in such and ask use one more time if we were willing to sacrafice for others.
I think this fails in a two ways, however. First, much of the sacrafice also plays into the mural-choice system of the game. Second, that the final question is basically "Are you done playing this character?", because we don't give a shit what they do or could have done afterwards.
A game that made the aftermath matter really well, but fucked up the end was actually Mass Effect 3. The two main topics of Shepard's final dialog with the Catalyst were: the galaxy's and Shepard's fate was sealed since the beginning, and that Shepard had to sacrifice himself if we wanted everyone to live. This doesn't fit, because Shepard just spent the last three games prooving that there's always another way, no matter what the problem is or what the odds are. It should have been the same. Just like F3 the stakes were set at their highest, if Shepard does succeed all sentient life will be wiped out. Did Shepard take the long road to prepare for the battle, or is he going to try going right at it.
Like I said, though, ME3 did do a better job of making us care about what happens afterwards. I genuinely didn't want to wipe out the Geth. I honestly wanted to see Shepard enjoying a drink with Garrus at the end, and going back home to Liara. After spending three games, and countless hours with the characters I wanted to see their story get wrapped up.
If I had to itemize it.
1) Make us care about the characters and what happens to them, so it's tragic.
2) Make it a key part of the story/theme/message
3) Fucking closure! There should be little questions left--unless you're going ambiguous and I don't want to go there.
This, of course, only applies to main characters, but some of the points are universal.
Dying is the biggest thing many characters do, it show be done right.
