Bioshock was great for so many reasons - some of the random moments when splicers would just be standing behind you did freak me out a little. I was too absorbed in the storyline to care as much, though. God, what a fun game that was.
Doom 3 was okay, most of it wasn't that scary to be honest. The scariest parts were when they would drop enemies right behind you (or have them jump out of somewhere). The scariest moment was when I was expecting to see my first Hell Knight, since it had been awhile since I had seen any other new types of enemies and the game felt like it should be getting near the end. I was really disappointed when they actually showed up and turned out to be big, slow pieces of shit that can't do anything. I was also disappointed when the Cyberdemon battle was basically an exercise in running around in a circle... he should have been smaller and a lot more aggressive, able to kill you easily if you don't find cover. Instead he just shot rockets in every direction *except* toward you.
Have never played System Shock 2, probably will never get the chance. Doom and Doom 2 were mindless fun, but definitely not scary. I also never played AvP or AvP2. Were Crysis or Farcry supposed to be scary? I viewed them kind of how I few Doom 2... just mindless entertainment.
Probably the scariest moment in gaming for me would have to be tied between a moment in Jedi Knight (where you fight your first underwater creature) and a moment in HL1 (when you fight your first underwater creature... damn shark things). I seriously get freaked out by underwater battles. It's the murkiness and being unable to see more than 10 feet, so you never know from which direction it's coming until it's already in your face.