My first game-ending failure wasn't your typical "ran out of food/drinks, lol" ending. Rather, it was a pretty epic confrontation, rather it was quite the opposite.
I had dug down into adamantine and was taking a few (baby) steps to mine it more safely, from my understanding of the game at the time. I wasn't carving fortifications or anything, but I had set all of my miners to masons so that I could immediately wall off in the event of a break into demons. Sure enough, we break into a small rock tube that extends deep into the earth, and the demons begin pouring out. However, my masons kept getting scared before they could finish the wall, and so we couldn't wall them off. The last adamantine miner, a she-dwarf, ran up and walled herself in with the feather worm demons to protect the rest of the fort.
Oddly enough, this story doesn't end in a tantrum spiral. Somehow... despite that the only entrance into caves and the demons had just been sealed off, the demons managed to make it to the surface, and ran right in through the front door! All of my dwarves were conscripted, and so underwent the longest battle I've ever seen in DF to date. "The Battle of Hours Ending" is what I would personally come to know it by. For literally hours, my dwarves did battle with... flying tentacled ash-blob demons. Three of them. For real hours, my dwarves fought these horrible creatures. Dwarves, cats, dogs and horses all joined the fray against the hell's angels. All of their tentacles were severed, but this battle had occurred literally just two days before the bugfix that would eventually weaken blob-type creatures composed of inorganic matter, and so my unarmed dwarves barely stood a chance. Yet, for the longest time they held out, and one dwarf would become a hero with his warhammer, though his name has been lost to the sands of time.
The battle ended in a victory for the demons after a whole swarm of their reinforcements arrived through the front door, but even when hugely outnumbered my unarmed, unarmored dwarves were stalwart enough to last for another fifteen minutes. I even drew a picture to commemorate the occasion. Still mentally trying to reason out how those demons could have possibly gotten out. I just assumed it was by magic.