I've only ever really been "scared" a few times in my entire experience of playing video games. (and no, I'm not trying to act like a "bad ass" here)
One of the first times I recall feeling uncomfortable or frightened was playing Doom 2 as a wee lad. I had never played a game like it before, so on top of the inexperience I had to face a plethora of grotesque demon and zombie creatures, as well as my first ever run-ins with monster closets. It definitely made me jump at times. Especially when I ran out of ammo. (I panic fired a lot)
The second time I recall being frightened, or rather, feeling both uneasy and experiencing jump scares, was playing Half-Life for the first time. I had never seen a game so deftly blend narrative and gameplay with such superb graphics (for it's time) before. So while playing it, I was often engrossed in the events on screen as well as enthralled by the spectacle.
Some of the most egregious moments in the game were some of the "reveal" moments. For example, the first time you come across a Gargantua. You're walking down that hallway, turning a corner to reveal a large room with rail tracks. As you approach the room, you see a soldier suddenly come running from one side of the room towards the other. Seconds later, before you have time to wonder why he's running, you see this twenty foot tall monstrosity with a glowing red eye running right after him, firing blue-orange flames from it's hands.
Yeah, I flipped a bit the first time I saw that.
Then there were the air-ducts and the headcrabs. I STILL hate those moments.
The last times I ever remember being "scared" by a game were playing Eternal Darkness on the Gamecube and playing a mod called Afraid of Monsters. In Eternal Darkness, the combination of the classic H.P. Lovecraft-style Old-Gods horror and the inventive psychological-madness effects messed with my head a lot. Didn't help that the first time I played it I was home sick with the flu, so most of the time I was already loopy and suffering from very mild hallucinations from the fever. Gave me some serious nightmares.
In Afraid of Monsters, there were a lot of jump-scares and twisted-imagery type monsters. All back-dropped by what appeared to be normal scenery, only devoid of any signs of life beyond the 'grotesqueries'. There was also a severe dearth of health, ammo, and batteries. (your flashlight had a limited amount of power, and when it ran out, you were SOL)
After those, and a few other occasional instances of scary games, I guess I became jaded or desensitized to the whole "horror" thing in games. I've yet to play one since that even made me mildly uneasy. It's a shame really, some of my most memorable moments in gaming were ones that genuinely scared me or gave me the "heeby jeebies". Another part of the problem is, most pure horror games, like Resident Evil, Silent Hill, etc, tend to be so over-the-top I have a seriously hard time taking any of it seriously or investing myself in the characters or story.