It was like, unexpected scary. It was just soooo different then everything else the initial shock and the shock halfway through were just crazy. It was nothing special, just freaky, kinda like your KOTOR 2 kinda unexpected thing, just less dead kids and more deformed primal monsters that are made from the essence of your lover/sister (I still can't tell what Erin is).
It reminded me kind of Outlast and the generator part.
Moira's Asylum was one of the few good areas in that game though.
I thought the majority of HL1 (NOT HL2) was pretty scary considering that it wasn't really a horror game per se, especially at the end when you have to go through the portal. Also there were parts of Dark Souls that made me just want to sit at firelink shrine and never leave, and I'm not even talking about the difficulty. New Londo Ruins freaked me out, those ghosts were just perfect, they could walk through walls and everything. You never knew when something was going to reach through the walls and skewer you.
Also, another DS one, the Tomb of Giants. The extreme darkness was almost unbearably claustrophobic, I was only able to make it through after resigning myself to the fact that something absolutely terrible was going to happen to me, and I might as well get it over with. I was right .
EDIT: Oh and +1 for the KOTOR 2 reference, I first played it when I was much younger and man, Peragus terrified me lol. It was probably the first "serious" game I had ever played, I just wasn't really prepared for it. All those bodies, that ambiance, and all those holotapes (yes I viewed every one) really built up the suspense. Oh, and invisible assassins. Ah, nostalgia.
Basically any vault in any Fallout game because I know I am going to be stuck there until Jesus comes back...and, since Jesus is fictitious character, I better pack a lunch. Navigation is not my strong suit on any given day and well, let's just be nice and say Fallout maps are about as useful as a Playboy full of Roseanne snapshots.
Well there are other historical records of his existence, even if you don't believe his teachings, but that's beside the point. My scariest was first fighting ghouls in Fallout 3. They charged me, and I had three 10mm bullets left and an almost broken police baton. Thank you stimpaks!
For me it was, somewhat bizarrely, on the Sims 3. I have no idea how it happened, but all of a sudden the screen had around five ghosts appear out of nowhere in my sim's kitchen and I didn't recognise any of them from anywhere. So I shut it down, uninstalled and got rid of it. It still freaks me out to this day.