I think Fallout 3 is a really scary game. Wouldn't you be scared if half of everyone was nuked to death and your constantly being attacked by raiders, nuked animals, and ghouls.
One of the more scarier games I've played was The Suffering and the Suffering 2 for the Playstation 2. It has a freaky mental aspect to the game and you can choose to be evil or good and ultimately change the outcome of the game. Highly suggest picking up this classic if you can.
Definitely F.E.A.R 1 and 2. Mortal Kombat Armageddon (i think it was), not because the games is scary but because one of the stages has this random giggle in the background the sounds like Michael Jackson and that freaks me out.
I debate this with my friends, as well, and I always hear them talking about F.E.A.R. 1 and F.E.A.R. 2 and how scary they are...
Needless to say, they've never played Dead Space... The first time around, I beat Dead Space on IMPOSSIBLE mode with a friend of mine---Took us about 18 hours of total played time on the game...
Everything about this game is scary. The creepy music/video at the menu; the introduction to the game, even before you get a weapon; and every single time a necromorph or lurker appears.
I still, to this day, jump out of my seat when things pop out at me in that game. Probably the scariest game of all time. Period.
I am a veteran gamer, and I have to say I do not scare easily. yet I remember some N64 quake game (quake 1, 2 I don't remember) that I played as a kid that was totally awesome. But it gave me nightmares all the time, like those huge aliens eating me (horrible dream for a kid). But now the only real game that can scare me is F.E.A.R or fear 2. Also doom 3 (maybe).
I do remember playing a rental for dead space. I change my mind, it' totally dead space. Thats the only game that tries to scare you at every turn. But it gets stale after a while and all I get anymore is the occasional "reflex jump"(where you get scared for about a second, then just keep playing).
In Half-Life two, the chapter, "Don't Go to Raven-Holm" I played that at about the age of 9-10, at the very begining, you see this lower-half of this body tied to a tree, and you can push it around. Also, the fast-zombies, they would scream, and that would square the living crap out of me.
In Dead Space, night time, alone, trying to beat some four armed pos down on the hardest mode, and than another one pops out of some vent, and starts eating my neck. Pretty messed up. When that was over, I just kept beating their bodies, and got an achievement. =D It also told me to get out of my crashed plane, and I thought it was kidding, and it killed me. =)
Zelda: The Ocurina of Time, I played that game at a very young age, and after you beat the first three bosses so that you can go forward in time, there these head-humping/choking zombies that wailed, and on the occasion you heard a scream. Scared the daylights out of me.
Quake: 4 That giagantic thing that yout fight on top of some building, and the end boss. Both were freaky, but quite easy to deal with. The gigantic thing, just killed it with the Dark Energy Emitter thing, died pretty quick. End boss was tricky but once I figured it out, he was a goner.
Although, everything would be alot scarier with a P.C.S.