I agree with those saying the "We Don't Go to Ravenholm" level in Half Life 2. It might not be super scary in the traditional sense, but it's pretty creepy either way.
There are parts of Resistance that are pretty scary too. I don't know the name of the level, but there's a part where you're indoors in like an underground base where they did experiments or something (there's lots of medical areas) that had my heart beating pretty hard. lol