For me, Ravenholm was scary as hell the first I played through it and didn't know what to expect (the fact that I wasn't as good at FPSs st the time helped). As for "that level" in Thief 3, I didn't find it all that scary, since the level didn't really alter the gameplay that much. I was very slow and cautious on all the levels, and ran at the first trouble, and if you play "that level" this way it's just not as scary since you aren't forced to make quick decisions.
My scariest moment in gaming has to be the first extended action sequence in Call of Cthulhu: Dark Corners of the Earth. Seriously, my hands were all shaking and sweaty by the time I beat it, and you could have heard my heart beating from across the room.