Finding out that not even a headshot will kill a zombie.
This doesn't sound scary, But imagine it:That Adrenaline rush from blowing the Shamblers head off, Then the sudden realization that its coming towards you, and then the sinking realization That, Barring a miracle, Your going to be torn apart, and theres nothing you can even do. and then you think about how your friends and family are probably in the same fix as you collapse against the wall, Covered in blood, And the decapitated Undead looming over you. Theres nothing you could do for them, either.
Personally, that particular scenario scares the hell out of me.