When I first played Resident Evil 2 I was terrified of Birkin, I was 10 at the time, but other than that, no games has actually scared me. Playing RE4 unnerved me a bit, and when I played Condemned, well, I hated playing that game, but in a good way. Hitting guys with a pipe felt real, and the atmosphere was great, I was on edge most of the time I played that game and I felt sick with the tension, but I wasn't exactly scared, just really wound up.
Actually playing through Lost in Nightmares for the first time, I was scared of that. I had no idea what was waiting for me in the mansion, or the courtyard, when I finally met an enemy, I screamed and paused the game to calm down, I ran off and hid, then the fucker hit me with an anchor, and I died. After completing it, all the tension was gone during my second playthrough. I really hope that RE6 is going to be more like Lost in Nightmares, but since it's going to be completely different to the other installments, then I suppose it is just a dream.