Ok, i'd like to start with, i don't often get scared by games, but i go hunting for the scariest things i can find. Personally i'm bothered by the fact that people get afraid of ACTION/survival games like Doom 3(OH god, me and my Massive friggen minigun versus that scrawny looking demon), and bioshock (you can shoot friggen bees nuff said). oh and personally Dead rising has more of a "WHO WANTS SOME!?" feel to it. OK now for my scariest games moments... hmm gotta say Fear, when Alma really gets in your @#$^ing head. especially with that goddamn ladder... i was afraid to turn around quickly for about a week.
OK next on the list goes straight to Condemned:Criminal Origins. Now heres a game that doesn't sacrifice combat to try... and be scary (silent hill). I love that your guy may for the most part be defenceless but he can still shove a 2x4 down the nearest crazys throat. This made levels like the farm house and the department store all the more terrifying. wondering when they're going to strike.
Now for a more obscure game. Call of Cthulu

ark corners of the earth. The entire opening segment of this game is littered with HORRIBLY FRUSTRATING stealth segments, and i gotta say, finding a gun is a beautiful moment.But anyhow this moment is one i found truely haunting. early in the game your character Jack "can't throw a punch" Carver accidently causes the death of a little girl. Insanity is a big thing in this game by the way. So Jack is in the car driving with a buddy, i decide to look up into the cabin, and guess whos in the friggen passanger seat; a mangeled little girl.. before i can blink she's gone. its that sorta stuff that made this game worth playing. Oh and ... when Daigon makes the scene in it, well allow me to quote what i think Jack would be saying "WHAT IN THE NAME OF CALAMARI JESUS IS THAT THING?!"
Oh and of coarse, in eternal darkness when it lies about the data being corrupt (lowest of blows in a game).
Oh and about my earlier comment, ya i find silent hill 2 to be a boring, frustrating adventure game with decent creature design. Wasn't really worth a play through for me.