The only game that ever managed to really evoke something approaching "being scared" in me was Amnesia.
Silent Hills, Resident Evils, Dooms, Dead Spaces, Metros, Stalkers, Fears, AvPs...nothing (good games though).
Cryostasis was fantastic too though, almost on the level of Amnesia. But...