I suppose from a dramatist's viewpoint it is a very effective way to close a story; I mean (SPOILER)FFX kind of did it with Tidus. Not to mention outside of video games, you've got Shakespeare (God blimey King Lear used protagonist death like it was candy) and other books and stuff.

It's a very popular endgame plot device, and can also be made even more effective if you get foreshadowing, the ailing protagonist slowly succumbing to a poison or something. It has to be done well, and have it creep on you slowly. A big surprise death isn't going to work so well. In this regard, FFXIII-2 actually handled it quite well I think. (SPOILER)Serah's eventual death is foreshadowed several times, so even though her death is surprising, it's not a "Oh Snap!" style death