Good question!
Now, this was waaaay back when, but a game that surprised me a lot was "Vampire: The Masquerade: Bloodlines". Before I bought this game, I was kind of a coward, easily intimidated by extreme violence. However, my affinity for vampires had always been very strong and so, after a lot of inner debating with myself, I chose to buy the game. Right from the first minute, it sucked me in with its rich atmosphere and good dialogue and characters, so my fear gradually subsided. I was a little frightened by the Blood-enemy in the museum, but my real first taste of apprehension came, like with so many other players, at the Oceanside Hotel. Although no real violence happened there, the level scared me deeply, but I managed to persevere and actually got quite a kick out of it afterwards. Even scarier to me was later the Zombie-level in Downtown, because back then, I had an enormous fear of zombies, no matter how detailed or not they were. Ultimately, it ended up becoming one of my favourite games, one I still return to now and then. It also helped me shed pretty much every fear in gaming I ever had.