The Morgue scene in Batman Arkham Asylum was rather unsettling.
=========
Ravenholm tends to impress on you the gravity of the situation once you enter, and see the dark quiet scene before you, hearing the cawing of crows and the weird music... after that it just becomes a rather fun and dark romp through a fast zombie filled town.
After getting onto a roof and thinking, hah! I have the highground, nasty zombies can't touch me here!
Then they raced up the gutter downpipes and tried to eat my face. AGAIN.
==========
The Shalebridge Cradle level in Thief 3 was one of constant nightmarish horror. That was before you get transported to the past and have to sneak around the same place, weaponless and with a distorted camera, avoiding silent dark figures.....
============
STALKER Shadow of Chernobyl had atmosphere you could cut with a knife. The constant but unfulfilled expectation of something jumping out on me as I rounded a corner was exhausting.
Then there was the couple of times where something does jump out...
The Sewers under Agroprom Research Institute, with the strange mind controller thing that jumps into the tunnel behind you....
And an incident in an area on the other side of the map, inside an old building near a larger ruin.
My thought process was as follows: Hmm, small creepy joint, lets check upstairs...
Upstairs: Hmm nastily dismemebered corpse on the floor, I wonder if what did that is still around.
What Did That: *BLOODSUCKER SCREAM*
I look around: Ocrap, *pulls out shotgun* They're invisible and fast, gonna back into that corner and wait till I see a shimmer in the air in front of me.
I move out of the tiny alcove and IT'S RIGHT FUCKING THERE and I squeeze off shots as fast as I can...
Left me seriously shaken for a while.
==========
I didn't find the Dunwich building especially scary, because my uber small guns skill let me kill em in one headshot with VATS, and the Fallout engine never really did the job for me.
Dead Space manages to keep up a constant creepiness, and fights with Necromorphs always leave me slightly paranoid and shaken, but like I always say, give a guy a gun to kill shit with, and that removes a lot of the fear and replaces it with a feeling of badassness.
Same with FEAR, the game was only mildly creepy because you could kill anything with a few well aimed shots.