It's like building sand castles on the tide and trying to keep everything running before the ocean consumes your Fortress (and then you drown to death too because this is DF). There is something wonderful about the permanence and consequences of all your actions, how even the smallest of things - a butterfly for example, trapped in a door lock, could spell the end of all your efforts.
Everything you do is consigned to destruction, and so that is why we must do even greater. From the moment I learned how to keep a Fortress alive until inevitable death I've found DF the definitive sandbox (on the ocean). Long it's been in my view that DF is about aiming high and digging deep, with the unspoken rule being accept what all farmers in adventurer towns will tell you - "It is inevitable."
The first memorable loss for me has to be when I embarked on some desert mesa-scape and fumbled around trying to figure out what hives were, how to use kennels and where all the water was going and why some Dwarves equally died of thirst and others drowned. Figuring the rest were doomed, the Fortress was abandoned to save the survivors.
The second memorable loss is one of my fondest losses. It was a desert with a river running through half the region, and my Dwarves were masters of it all. The great giant badgers were a fearsome menace, but were kept in check with war dogs. The giant badgers that once ravaged my Dwarves' lands and killed them limb from limb were being hunted by fearsome veterans of the badger wars - and not all would ever return from any one hunt.
Underground the industrious Dwarves grew vast stores of produce and alcohol, with rooms for all and even plans to produce an even deeper sub-Fortress complex with a 30zlvl walkway, whilst aboveground a Colosseum was constructed for reverence, and a grand workshop area built on the southern side to develop the Fortress economy. Everything was going grand! Until one fated hunt, where the giant badgers did not flee from the dogs but instead attacked head on. Though the badgers were fought off, they took many Dwarves down with them and even more dogs. The hunts ceased, and the Fortress relied more on traps. Then one accidental mass-release of honey badgers within the Colosseum resulted in the deaths of several craftsdwarves before the war dogs and soldiers managed to scare them off. Things started looking bad, but to me everything was going brilliantly. The Fortress was wealthy and continually expanding, and tombs given to honour the fallen.
When winter came the river froze over and the badgers roamed over the river into safe Dwarven land. More Dwarves and dogs were lost, the Dwarves were so afflicted that they did not complete the fortifications in the workshops in time. The Dwarves held on against the frozen river and the badger onslaught until the goblins arrived. I had never seen goblins before, and it really was the first time I gathered what kind of game DF was. My Dwarves were hunters, not soldiers; their weapons were of bone and ivory, the goblins carried metal. I was astounded by how the Dwarves I had seen flawlessly emerge from victory were cut down by these conquistadors, even the myriad army of dogs did not suffice to much. The captain himself, bone helm, warhammer held high atop gargantuan piles of greater beasts - even he was no match for the speargoblin captain. Because the workshop fortifications were incomplete the entire Fortress was compromised and the goblins had free reign. The only survivor was a lone miner who escaped the carnage underground; he sealed himself off from the rest of the Fort but was eventually caught trying to secure the food stocks by the goblins. He surprised the goblin captain and cut his arm before being beset upon by so many spears - the Fortress crumbled to its end. I had learned how powerful metal was.
Under the eclipse of this ruined Fortress I had learned that losing is Fun.