Great thread.
D on Saturn gave my friend nightmares for months, but it didn't bother me for some reason (I suppose that it didn't help the rich sap since he played it at 2 am with full surround sound in his basement alone). Lunacy was also the same way.
Clock Tower on PS 1 was pretty intense in a good way, since you don't have any real way of fighting back against your foe. You really had to think on your feet, and learn to use your environment to your advantage. Haunting Ground on PS2 was another attempt at this genre and it was pretty scary. It's scary because you are pretty much helpless. The panic mode where the entire screen does a Blaire Witch project thing and goes black and white and starts shaking really adds to the intense atmosphere.
Silent Hill 1 or 2 ( couldn't remember which) did scare me a bit, but all I remember from it was been annoyed by one of the puzzles.
The Resident Evil series wasn't really scary at all, especially as time goes on, it became less horror, and more action. Most of the time I was worried about my ammo management. The Outbreak subseries did provide some intense moments, since it's the first time you are timed, therefore making the idea of survival much more urgent. Of course, D on Saturn was timed too, so maybe the idea came from there.
The only ones that really gets to me is probably the Fatal Frame series and Siren. I don't know why, but it's probably because childhood associations, so I find stuff like this much more scary than traditional Western slashers or zombies. I still don't play Fatal Frame later in the night. I would probably get freaked out if I did try. Siren is another ballgame altogether. The game just gives me a headache, and creeps me the f**k out from time to time. The game is a pain in the arse to play too, so I couldn't believe it when my little bro finished it. He did confessed that he always played with a friend present, and never at late nights.
The honourable mention for creepy, gloomy and scary things from non horrors would probably go to Shadow Hearts 1 on PS2, Persona 1 on PS1, and maybe Daggerfall on PC. Shadow Hearts 1 had one of the best dark, and creepy atmosphere for a RPG I've ever seen. One cut scene in the game gave my little brother nightwares for weeks on end. It's too bad that SH 2 and 3 went a lot more light hearted and with more of a focus on humour. Personal 1 just had this sense of gloom that permeates the game. Daggerfall was an interesting case for me. It wasn't scary per se, but the sound effects were decent enough to make me jump on occasions. Dungeon crawling at low lvls was also a pretty intense experience. It was an interesting experience to go out at night at the capitol though.