Our best fumble was actually a series of fumbles that happened to the same character in non-combat situations during the same game session.
This was the same party that faced the chaos commander in my previous tale. The rogue, much quieter than before, had recovered from his concussion by this time and rejoined the group. They were exploring a mountain range to the north of their base town, following up on reports of ruins in the area. They'd been moving through thick pine forest for three days, and were attacked twice by wolves and once by an Orc scouting party. So they were a bit nervous...
In the van was the dwarf. Since they were moving into mountains he was in all his glory, playing the character to the hilt and making much of how his background would save them. Even though he was a fighter, he'd managed to spot one group of wolves before the Steppelander rogue and the Orcs (which allowed the party to actually ambush them), so he was really confident about his perception skills.
It was an overcast day, and the party moved out in their now-standard formation (the dwarf up front with his crossbow ready, followed by the Steppelander with her composite bow and the rest ranged behind with the hungover bard NPC bringing up the rear). The dwarf moves out in high spirits, making Perception checks every so often (when the faint trail they were following made a switchback, or if there was a particularly large outcropping to the front...things like that). So far, so good...except for the roll he made when checking a particularly large pile of deadfall. He rolls..."crap"...the dreaded 01. Rolls again...94. Absolute Failure. Peering into the darkness at the center of the pile of dead trees (the result of a wind storm decades gone), he sees two glittering eyes....it's... "A bear!" The dwarf shouts, fires his crossbow blindly into the dead trees, turns, and runs. "It's a big 'un! Get clear!"
The party freaks out, gets bows ready, and the hungover bard falls from his horse. The dwarf streaks past the Steppelander, who brings her bow to full draw and shouts for the other rogue to come up. The dead wood shifts, branches fall, and a very bewildered possum staggers out and peers around. The dwarf, back by the now-standing bard, continues to insist that there was a bear back there, and his shot must have scared it away.
Once the possum ambled away, the party composed itself and moved on, with the somewhat subdued dwarf continuing in the lead. Soon the trees began to fall away as they moved higher into the mountains. The landscape changes worried the party for some reason (possibly because they'd been attacked before in similar terrain), so they asked the dwarf to check the composition of the nearby rocks. They'd also heard rumors of "gold in them thar hills," and wanted to see if there was any chance of such. The dwarf swaggered up to an outcropping, dropped to one knee, and...02! The second roll...92. The dwarf looks closely at the rock, rubs it with a hand, sniffs some of the bits, and turns back to the party. "Obsidian," he announces with total confidence. "Pure obsidian."
After that, the dwarf was constantly getting obsidian shards in his bedroll, or drawings of bears tucked into his gear. Any perception roll was met with cries of "It's an obsidian bear!" or "Have the dwarf check." We also had some spectacular combat and spell fumbles over the years, but no sequence of fumbles had the same impact on the group as those two failed perception rolls.