I stopped being scared of Ravenholme at exactly the same time I found the sawblades, sawblades + gravgun + Zombies... Hey that ones head fell off, oooooh.
Then of course the gas canisters where a complete riot also.
Scariest games though, Doom3 was scary until you got used to it, and then it was just edge of nerves stuff. For instance it was , oh look a black corridor, and a room without lights. Great I know something is going to jump out on me, oh yes it did, it still made me jump though. I could only play it for about an hour at a time, at which point I had to put it down. It wasnt fear but just pure twitchyness.
FEAR though, that was more scary. Where Doom3 was a glut of scariness, FEAR was more subtle and like to throw the scary bits at you carefully and definately not at expected times. Consequently I hate that little girl!.
Other very good scary games are (any of) either of the AvP games.
I say any becuase I originally played AvP on the Jaguar64, and then it got ported to the Playstation. Way before the PC versions saw the light of day. Playing as the marine is good for a scary game, the alien or Predator has a completely different feel.
I especially liked the first few missions as the marine, great tension builders, someone really took the atmosphere from aliens.