Well, one of my recent megaprojects very nearly failed... I dug out about 45k stones worth of room (a huge circular cavern with a big pillar in the middle) and, as planned went to fill the bottom 2 z layers with magma. I think a diagram will better illustrate...
WWWWWWW WWW W W=wall _ = floor
WWWWWWW WWWW W --- = bridge M = magma(of course) L = lever (ill get to that in a moment)
WL_____ __----------_WWWW W
W___ _____WMMMMM WWWWWMMMMMMMW
WWWMMMMMMMMMMMWWWWWMMMMMMMW
Now, due to the wall, the magma would not come up through the below-magma-level-area which was my magma forge location. As it was filling extremely slowly, I decided to speed things up with 3 pumps to greatly increase the flow of magma from the pipe to the cavern. This lead to the magma becoming pressurized.
A couple things to note at this time: 1. That lever was to turn off the pumps. 2. The only way to that area was via the bridge from the central pillar (which had staircases to higher levels with bridges which went to the rest of the fort) 3. The living quarters for my dwarves were relatively far away (as in a couple hundred tiles to walk to get from the living quarters to the lever).
Now, when the magma filled up level 2 all the way... I was a bit slow in sending a dwarf to pull the lever to turn off the pumps. The pressurized lava (pressurized I think to level 4) began to fill up the 3rd level with tiles of 1's. By the time a dwarf managed to get into the central pillar to turn off the pumps (I think there were hauling stuff from a recent goblin attack at the time), most of level 3 was covered in 1's. The dwarf managed to make it to the far side of the bridge... where he was cut off by magma in front of him about 2 tiles from the end of the bridge. More lava then proceeded to cut off his escape from behind, and he was then burned to death as another tile of lava landed on him. Not only did this cut off the bridge (my only access to the chambers with the pump levers) but it also proceeded to melt the bridge, since it was made of non-magma safe materials. I then told my dwarves to mine their way in via the main stairway into the cavern (which happens to be directly above the area in question). At this point, the 1's were beginning to turn into 2's and flood 1's into the room with the lever. Luckily, a pair of dwarves managed to run past and pull the lever. Well, one did anyway, I think the other was after a sock.
Edit: here is a picture i took when level 2 was about half full, the area in question is a few levels under the main entrance to the area, which is just left of center in this picture.
Hmm... other fails... Probably one of my early forts. I tried to drain an aquifer. Bad idea. Worse than embarking with 50 cats.
Then another fail when I was playing with orc mod on another map. I had a great 3 drawbridge airlock system around my trading depot. Unfortunately for me, I hadn't hooked up the inner most drawbridge, which I did not find out until orcs had run past the first 2. That single band of orcs took my population down from over 100 to about 12. A couple seasons after that (when the tantrumming stopped for the most part, yay for now vacent noble's quarters!) an elven trading caravan came. By this time I was barely stuggling by, since due to the huge losses I had taken, I had no immigrants, and so any supplies were welcome. Unfortunately, the elves brought a goblin ambush with them, and me being over-eager for elven goods had not shut the outer bridge of the airlock system. A handful of goblins got in, slaughtered the elves, and then proceeded to take out bunches of dwarves. By the time the last goblin died, I was down to 6 dwarves. Of that 6, 4 were bedridden due to wounds, leaving a legendary miner and legendary child as the only dwarves capable of working, and since children can't be told what to do... It was more like 1.25 work-capable dwarves. Well, after a few months 2 of the 4 wounded died of hunger/thirst, leaving me with 2 wounded and 2 whole. After a few years of isolation, the 2 wounded eventually got better (and the legendary child turned into a peasant) and the dwarves managed to eek out a living. It took I think around 3 years for any migrants to brave the dangerous place which was my fort.