I can think of really only three games that really freaked me out: Eternal Darkness, F.E.A.R., and Left 4 Dead.
There are others games that have made me tense, but these are the only three games I can think of off the top of my head that really did something to me.
Eternal Darkness - Aside from the freaky sanity system (which freaked me out a few times too), this was one of the first games I played with surround sound. Playing this game with all the lights off really did it for me. I remember playing this with my old roommate in the room and we were freaked the fuck out. There was a point we actually thought there was someone on the balcony.
F.E.A.R. - This one just had some good atmosphere. I played this one with no lights and noise canceling head phones which just made for a good experience (actually work one of the guys who worked on it too 
).
Left 4 Dead - I almost crapped my pants when the zombies broke through the wall in the hospital while waiting for the elevator.
