I never really considered the asylum to be all that creepy. I mean certainly it was moreso than the rest of the Thief 3 game, but it was still somewhat lacking imo. The inmates were supposed to be creepy and terrifying, but once I realised how easy they were to kill, I just went on a mission of genocide, which left me me free to explore the place without interruption.
Thief 2 also never creeped me out, not even "Blood" or the last level which people were raving about. Thief 1 on the other hand, had me a mass of tightly bunched nerves the whole way through. None moreso than the levels with undead and spiders though. I shamefully admit that I never completed TDP. The Bone Hoard (I think that was the name) scared the crap out of me (I somehow attracted half the zombies in the tomb and spent considerable time running laps around this big pit with about 20 of them in tow) and by the time I got to the level where you had to go "over the wall" to the sealed section of the city, I just chickened out. After running like a little girl from a couple of hammer haunts, I got completely lost and ended up hiding in a dark alleyway where I could hear a spider somewhere nearby. I couldn't work out where the damn thing was (other than really bloody close judging by the noise) and was too chicken**** to leave my hiding place to find out in case it was just around the next corner.
Granted, I played TDP before either of the other games and I was certainly far younger so perhaps it's a case of "rose coloured glasses". In any case though, both Thief 1 & 2 are easily contenders for the most atmospheric (and immersive) games ever made. Thief 3 though, just didn't do it for me. It looked pretty, but the tiny consolised levels meant a loading screen and a new area every few minutes and nothing kills it for you more than that.
System Shock 2's also up there for atmosphere. Same company as Thief, what a happy coincidence... or is it?
F.E.A.R. also had a good atmosphere, but it's overdone. You get really desensitised to the office environment by the end of the second building. I'd actually forgotten that there were two buildings at all, they all just blended together. The bunker levels though were fantastic imo. The downside to it all though was your lack of vulnerability. It's hard to be nervous when you're a super fast killing machine with ultra-lethal weaponry and a selection of instant kill kung fu moves. I still enjoyed it immensely, but the player being a god among men really does change the way a game plays.