I've got a couple of these.
The first is the one that happened to me.
Kyle was a cleric in a D&D 3.5 megadungeon campaign run by my friend Kris. I was determined to "old school" it by describing my interactions with the dungeon rather than relying on dice rolls. This proved to be Kyle's undoing the session when the party rogue didn't show up/
Kyle took it upon himself to do any searching and whatnot that needed doing. So when we came to a door and we asked Kris "Is it locked?" he asked me "How are you searching?" I tell him that I'm trying the knob to see if it moved.
The door was not locked, but my rattling had alerted the hydra (with some white dragon ancestry. There was a white dragon somewhere that got around quite a bit on that level of the dungeon as I recall) on the other side, so when Kyle opened the door, he was immediately blasted with ice breath.
I took my lumps with grace and rolled up a new character for the next session.
Both of these next stories were character deaths that I oversaw in campaigns that I ran.
The first character death in a campaign I ran would have been Scout. The funny thing is that I was running Cartoon Action Hour as the system, and death is not typically on the table in that game.
But it was the last session of this campaign and we were going for the big finish. The premise of the setting is that in the distant past, a technomagical civilization (similar to a mythic Atlantis) had been invaded by insectoid psychic aliens (Good old invader from Mars). There was an uneasy peace in the setting, but there was trade and other things making all-out war undesirable.
The villain of the campaign was a psychically preserved general who had been revived and sent from the Homeworld to reignite the war and ultimately win it. He had a crystalline body that he was able to operate and inhabit, as well as new war machines. Among these were flying saucers that were piloted by telekinesis.
One of the party members was Scout, who was actually one of the psychic aliens who had grown up on this planet and his Queen didn't want the war to restart, so he teamed up with the rest of the party to stop the General's plans.
Since Scout was an alien, he did have the ability to pilot the flying saucers. So during the big climax, he sneaks aboard one of the flying saucers and commandeers it. I assume he's going to use the craft's weapons (telekinetic force rays, I think) on the general, but he declares that he is 100% crashing the flying saucer into the General.
I decided that was suitably dramatic that it made a good death for a game that didn't normally include death and also cued up my CD of the soundtrack to Transformers: The Movie (the good Transformers movie) and played The Touch by Stan Bush.
Some time after that, I started my Castle of the Mad Archmage campaign, having no idea how long it was going to go.
Fairly early in, I had invited Scout's player. a friend of mine named Mike, to join us. He rolled up a gnome thief/illusionist named Bentley, who went into the dungeons under the castle with Ashlee's ranger who everyone just called Boots/
The reason the character was called Boots was because of her Strength score. It was 18/76, which put her in the second highest possible bracket for Strength. Her chance to kick down doors in the dungeon was nigh legendary.
One of the doors that Boots kicked down had a small room full of animated skeletons behind it. Battle ensued and Bentley got himself into the fray quickly. Boots had a tougher time, since there were so many skeletons to fight. Ashlee asked me if I would let Boots get into the room where the skeletons were, so she could fight them properly. I told her that if she could defeat one of the skeletons, I would allow her to move into its space immediately. This should have been pretty routine, since her Strength also boosted her ability to hit and damage enemies.
But then she rolled a 1. The worst possible result! To make things interesting, I said "Ouch! That means that you have a chance to accidentally hit Bentley instead of a skeleton. Make another roll to hit."
Natural 20. Best possible result. Double damage and a free toaster! (Well, maybe not the toaster) Even just doubling the flat bonuses from her Strength score was more than enough to put Bentley below 0 hit points. Allowing her to roll the damage from her weapon would have been overkill.
It was such a surprising and unexpected turn of events that even though Mike had just lost a character, he was laughing in surprise with the rest of us.