Half Life 1 had some really effective scary moments, even when you remember them they still jump out at you. Half Life 2's Ravenholm is a great horror section as Powersoft said, I always thought that would make a great expansion, like a survival horror episode based in Ravenholm.
One old game that scared me was Captive on the PC, ST, and Amiga, it was sorta like futuristic Dungeon Master, but while your fighting these little goblin aliens and broccoli monsters, ED-209 will suddenly appear and eat your lunch. I think the scare factor came from the amount of control you have over your characters, you can swap heads and legs and stuff, plug chips into their brains, arm them to the teeth - but then your playing in a really claustrophobic setting where there's often no place to run. That was a damn fine game, nothing since Captive has eaten up anything close to the time I spent playing it.
Modern horror games tend to be a bit wishy-washy though, there are exceptions like HL2 and FEAR, but it's almost like they can't get away from the cliches...
Monster your about to fight runs past in the distance.
Streaks of blood on tiled floors.
Toilets with 1 occupied cubicle.
Hapless fodder being owned behind glass barriers or fences.
I don't think any of the above have actually scared me, but I see them in practically every FPS. I enjoyed RE4 because although a lot of the survival horror elements were lost in favor of action, the claustrophobia and amazing atmosphere kept the tension going. We're too desensitized these days to be scared very much, I think creepy is the way to go, have stuff crawl under skin, a nasty facial injury is more disturbing than a decapitation.
There's more effective ways to scare someone with stuff closer to home than any OTT monster could do. There's a girl I work beside who has the scariest elbows you could ever imagine, they bend the wrong way by about 45 degrees! - it would give a Freddie Kruger nightmares. If your looking for fresh inspiration I suggest the SAW trilogy and Pan's Labyrinth.
