Resident Evil 4, with the Regenerators.
You're all like 'Hmmm, so before I fight these things, I'll get an infra-red scope to kill 'em with'. Then, the b******s start getting up and you're all RvB-esuqe like 'Oh crap, oh crap, oh crap, oh crap, running, running, running!'
I don't play a lot of scary games. I also don't find some 'scary' parts that frightening either.
B = EoCWASO x AICW
Where B = Bravery
EoCWASO = Effectiveness of Current Weapon Against Scare Offender
AICW = Ammo In Current Weapon
For example, in the Ravenholm situation, you are given saw blades at the start of the level. Play it correctly, and you can make one of them last for a significant portion of the level. When using this saw blade, AICW is infinite, given how you can use one blade over and over and over again. The blades also instantly kill most enemies, with the Poison Zombies taking two hits to kill, so EoCWASO is very high as well. So long as I have one of those blades on hand, nothing scares me in Ravenholm. I lose it however, and I'm out of there.
Having the Gravity Gun also means headcrabs aren't scary anymore, seeing as the primary fire is capable of killing one of 'em in 3 shots.