Everyone has a different take on character deaths in RPGs. I've been gaming for over twenty years and have seen everything from players who want unlimited protection from death to those who prefer meat grinder campaigns where only the clever or lucky survive. While the world is big enough to support both extremes and everything in the middle, this topic is a frequent source of argument at the gaming table (and endless flamewars on the Internet). Therefore I think it is important for game masters to listen to their players preferences and find an approach that works for the group. With that in mind I would like to advocate my own position on the issue, which is this: please kill my character.
Maybe it is because I come from I slightly competitive background, but one thing I can't stand in an RPG is the GM using kid gloves or offering script immunity to PCs. To those unfamiliar with the term, script immunity means the PCs, by virtue of being the "stars" of the show, have varying levels of protection from death. Sometimes this means the GM only let's them die when they take foolish risks, but it might also mean defacto invulnerability. It can arise because the players themselves don't want to lose characters they've invested hours to months developing, or it can arise because the GM doesn't want to deal with the hassle of having the flow of the game disrupted by loss of an important PC (and for a host of other reasons as well). In my opinion, this strips the game of most of it's fun.
Yes, character death is an inconvenience and few shout for joy when their beloved PC bites the dust. But removing it from the game reduces the stakes of the game, which for many of us also reduces the thrill of adventure. When the outcome isn't a foregone conclusion things are just more exciting. Doubly so when you the outcome includes the possibility of a PC kicking the bucket. To me script immunity is like playing a video game with the cheat codes on. The luster and the excitement vanish when there is no true threat facing my character (sure the threat can be other things like loss of reputation or power, but pound for pound, nothing matches character death).
Character death immerses you in the game. When the protective hand of the GM is obvious and characters don't die when they clearly should, it disrupts my suspension of disbelief. If my dwarf dives onto the back of a dragon and falls down a thousand foot shaft, he should die, he shouldn't make a surprise appearance later in the adventure. If my secret agent takes a grenade to the face and the damage is enough to kill him, please don't have him wake up in a hospital bed down town. I'd rather my character die than disrupt my immersion in the setting.
Besides the obvious reasons of immersion and excitement, there are a couple of less obvious reasons character death makes the game better.
The first is that makes your heroic character that much more heroic. If your character isn't truly risking his life, then how much claim does he have to greatness when he is a 16th level slayer of dragons. If the dragons were never capable of killing him in the first place, it drains the heroism from his exploits. But if the dragon slayer was in peril every step of the way, always at the mercy of the dice, that makes his accomplishment truly badass.
The second reason is character deaths provide fun stories to tell by the table. My best gaming stories revolve around the final moments (profound and not so profound) of my favorite characters. There is something to be said for regaling your friends with the tale of Evander Mightyfist's graceless slip into a pool of acid.
So don't be afraid of character death. If you have been hesitant about it in the past, give it a try. Run a "let the dice fall where they may" game and see if it amps up the excitement a bit.