My scariest moment was when I joined a war between the Greeks and the Russians, both neighboring me.
Since the war was in modern age all of us had nukes, and, well, all of us used them. Turn after turn the Russians sent waves of nukes, themselves being hit by Greek nukes and mine.
This is up to today the most full scale nuclear war I ever encountered in a Civ game.
The really scary part was the aftermath, because due to all the pollution, about every two or three turns a global warming occured, turning all of my beautiful grassland into desert.

And I didn't have enough settlers to clean up the tiles so fast! Food production declined, my people died from hunger, money revenue declined, I couldn't support the infrastructure of my cities, and so on.
It all happend so fast that I really feared the game could end in a nuclear winter scenario, because we destroyed the planet. We didn't, but my final score hit the bottom due to all the pollution ...
Another scary part was when my then-beloved obviously went out of bed and caught me playing Civ in the middle of the night, asking: "Are you still playing?"
Boy,
this situation is really scary!
