I got a couple....
On my very first fortress, I got bored in the second year because I didn't like the way it was going, and decided to flood the fort. As I was admiring my handiwork, I noticed a single kitten trapped by the water in the long hallway leading to my crypts. It kept struggling against the current, trying to reach the stairs, but was pushed back each time. Eventually, it just sulked at the end of the cavern, next to the coffins, and waited for the hall to fill with water. Hell of an introduction to DF that was...
Another time, I was doing a variation on the Hermit challenge, using two dwarves, one male, one female, and killing off all the migrants, with the hopes that they would marry and live like villains in a fairy tale, killing off anyone who trespassed on their land (maybe someday I mod it so I can build a house out of ≡Plump Helmet blocks≡). Unfortunately, while digging out the new and improve migrant murdering room, the man built a floodgate from the wrong side and ended up drowning when the river thawed. The female set out to finish the deed, only to be ambushed by Kobalds. After charging them and killing one, she came to resemble a pincushion from all the arrows, and tragically bled to death. I had big plans for that fort, and between a quick thaw, and some rats with sharp sticks, they were ruined.
And finally, though this isn't really Fortress mode, I once had a Human swordsman adventurer designed to be a sort of mentor to drunks, gathering a crowd around him, and then nurturing them until they became forces to be reckoned with. Unfortunately, on the first quest, all 4 drunks were slaughtered by the Cyclops before he even could get into combat to save them. Saddened and disheartened, he traveled north, in a self imposed exile. Along the way, a friggen' wolf bit his eye out, and he suffered from chronic pain that would make him pass out at random occasions. My worst fear was that he would pass out in the middle of wrestling a naked mole dog and face an ignoble end. So I made the best of his remaining days and did quests for a town I happened upon. For some reason, this town had a ton of enemies, and so needed me to kill them all. After killing two minotaurs and an ettin, I was on my way to kill a cyclops. Unfortunately, I held down the down key too long, which the game interpreted as me wanting to walk directly past said cyclops without attacking it. Before I could even stop, my poor one-eyed swordsman's chest exploded in gore, and that was that.
The most tragic things to me are the ones that either are horrible dragged out, or happen so suddenly, you don't know what happened. And all too often, that's the way things happen in Dwarf Fort.