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
I remember that level. I was pretty scared because they had so many tunnels leading off in different directions, and I didn't know when a door was gonna bust open with some Chimera running out. They also had those crazy fast Chimera that could climb walls in that level.