The Worst & Most Frustrating Ways To Kill Off Main Characters
Killing characters off is a good way to add dramatic tension and promote character development... or it can be a really good way to frustrate audiences for years to come. So, after thinking up some of the most frustrating and pointless character deaths I could think of and asking various people which deaths they felt were pointless or frustrating, it all comes down to a few common elements.
Sudden deaths out of nowhere for long-running beloved characters
EG, Cyclops's death in X-Men: The Last Stand, Captain Kirk's death in Star Trek: Generations, and Wash and Book's deaths in Serenity. Deaths like these tend to be done for two reasons - one is for simple shock value (a bad motivation for killing off the character from the start), and the other is because an actor was no longer available and... the writers got lazy and uncreative. The general sentiment is that epic characters need and deserve epic deaths - anything else is generally perceived as anticlimactic.
That said, you can more easily get away with a sudden character death if peoples' reactions to it are proportionate. For example, when Dr. Janet Frasier of Stargate SG-1 died on the battlefield, other characters were shown reacting to her death throughout the episode, and at the end a fitting memorial was given. Similarly, in Buffy the Vampire Slayer, when Buffy's mother died of an aneurysm, characters were shown mourning and grieving. In both cases, the characters and their deaths were not simply brushed aside as if they were inconsequential - in fact, each of them had an entire episode dedicated to the fallout from their deaths.
Characters who die due to sudden lapses in common sense or competence that remain unexplained or unacknowledged
For example, previously-established phlebotinum that could have saved a character's life is mysteriously forgotten, or a character dies due to an uncharacteristic lapse in judgment that goes unacknowledged by others, or perhaps an established badass is quickly picked off without even putting up any real resistance or is killed by someone no more powerful or competent the character has fought and defeated before. In any case, the audience is left feeling like the character died for no real reason.
The best way to avoid the forgotten phlebotinum problem is to set limits to your phlebotinum from the start. Perhaps the life-saving herb is rare and difficult to obtain. Perhaps it requires a rare fuel source. Check out Phlebotinum-Development Questions so you can ask yourself the right questions to avoid this problem from the get-go. If the phlebotinum has already been developed, then at least come up with a reason why it couldn't be used - eg, perhaps it wasn't within reach.
For the other two examples, aim to write a death that doesn't require the character's common sense competence to suddenly take a nosedive in some way. If it's supposed to be something is overlooked, try to come up with something it makes sense to overlook, or create a situation where overlooking it actually makes sense. If it's supposed to be because someone kills this character, to create someone legitimately strong or skilled enough to actually present a legitimate challenge to this character even on a good day, and don't let the character go down without a fight.
"Take that, audience!" deaths
Essentially, when writers kill off a character for no other reason than to prove a point - perhaps they want to prove that they're so stone-hearted that they CAN kill anyone if they so choose, or they want to prove that their worlds are gruesome, gritty, and grimdark by making a token sacrifice of some character, particularly the cute and chipper one.
Deaths that drive neither plot nor character progression
Deaths that for one reason or another, do not forward the plot or prompt character development in any way. It may be that the death occurs right at the end of the series (particularly if it's not the kind of situation you'd expect at least someone to die in, or if it basically involves removing someone's plot armor), or that the death is quickly forgotten about by the characters - EG, they may angst about it for a short while, but after their moment of angst is over, it's business as usual, and for all the impact to the plot the death had, it might as well have never happened at all.