pmcollectorboy: Picking up a gold rupee when your money bag is maxed out.
Opening a chest when you have maximum arrows/bombs only to find out that it has more arrows/bombs that were just wasted. (Happens a lot in many Zelda games.)
ApexProcyon: When you're rolling stats for a new character in Baldur's Gate and accidentaly press "reroll" after you have rolled 95. Baldur's Gate (or life) doesn't get any more emotional than that.
Similar things in many games.
Although I haven't played it, how about, from Final Fantasy 8:
Using Selphie's Slots limit break, seeing The End, and pressing "Do over" before you realized it.
(For those who aren't familiar with the game, Slots is a limit break that gives you a random spell, but has a "Do over" option if you'd rather get a different spell. The End is a spell, only available via this limit break (you can't get it normally without hacking), that is guaranteed to kill all non-undead enemies that you're fighting, including bosses (though, for the final battle, it only kills the current phase).)
IwubCheeze: ......dying to a boss where the save point is before a long and unskippable cutscene
Edit: I suppose the same could go for dying and having to endure long times.
That's a sign of bad game design, so I would argue this is largely the fault of the developers. (FF10 has a bad examplle of this; there is one part where there's an unskippable 5 minute cutscene (seriously, I timed it from Youtube videos), followed by a 3 phase boss fight, and the third phase opens with an attack that is likely game over for a player not expecting it.)