The most recent adventure that I've played that really frightened me was Broken Sword 1.
Spoiler:Some parts like: being trapped in the Hotel Ubu because if you went out, you would get killed; seeing Kahn at the entrance to the cave in Syria; and the train sequence was really scary too.
I guess the reason why was because I could really "get behind" George while playing the game; like I was actually playing him, to sound cheesy; and when he got in trouble, I was frightened. The spooky music really helped to develop the scary mood too.