I think I have three that I can think back on particularly, nothing spectacular.
First was when I located the cache down to a corner of a metal railing, so I assumed the cache would be a magnetic one. I ran my hand over the sides and back of the fence, found something solid and went "Aha!" - pulled it out, it was a particularly big snail. Turned out the cache was not magnetic but placed below the railing.
Second time I "failed" I did also find the cache. I was in Pelsall in the West Midlands in England. I was staying with friends but took time out alone to go geocaching. They had warned me about the red mud beside the canal, but would I listen? I did see a well followed path, but I decided to follow my compass the direct line to the cache. By the time I realised the red mud was not a local legend, I was too far in and committed to turn back. I kept going, and going, up to my ankles in red mud by now, and retrieved the cache. It said in it's description that it contained trade items and a log. The only thing in it was an earthworm. I didn't trade and take him with me, I released him. Then I also burrowed my way back out of the red mud. Rather shameful coming back to a friend's rented home (leaving a red trail behind) with such muddy feet.
My biggest fail involved my own cache. It has been found by four people in the last twelve months, each of them logged it as needing maintenance. I've been there four times to replace it, but.... I can't find it! Not for searching high or low. It's obviously not that hard to find, I never get DNFs, but can I sniff it out? Of course not. That's almost too tragic to be comical!