Guys, my take is that each person is different as to how much he is scared in video games. I remember games that i was scared while my friends were not. And of course age has to do a lot with it.
For example, Alien Trilogy (PSX) had me sitting at the edge of the seat while i was waiting for the motion tracker to kick in when an alien was close. Still, a lot of my friends didn't think that the game was scary enough at the time.
A good example of how a game used to be scary is Realms of the Haunting. That game was scary at the time. SCARY! But if you play it now for the first time, i doubt you'll be scared.. But it is a great game and highly recommended by the way! ;-)
Lastly, a recent example of a game that scared me much was Alan Wake. I haven't thought that a 3rd person could scare me but this game did it. I felt anxiety while i was playing it, i was seriously immersed in it's world, the atmosphere was great and there were a lot of instances where i couldn't progress in the game because i was scared, especially when i was tired. Still, a friend who played it told me that it wasn't scary at all and Resident Evil scared him much more.. What can i say, to each his own...