The two games in recent times that have made me almost shit a brick from being scared would be Bioshock and RE4:
Bioshock, the room misted up, turned around and a doctor was about to inject me with a syringe. I almost shat a brick at that. I think I set the corpse on fire several times in rage and fear.
RE4: I was up to a pretty scary bit, when the lightbulb in my room fell out of the socket and almost hit me in the back of the head. Okay, yeah, so it was something that happened in real life that made me scared, but it stopped me playing the game out of fear.
F1 2009 (Wii): F1 2009 hands-on