The scariest game I've played would probably be Horror at Harwicke Castle, a Call of Cthulhu scenario by Andre Kruppa. Andre runs his games in the dark, using professional lighting rigs, sound effects, and, sometimes, a fog machine.
I played this one at Carnage in Wonderland (2011). My character didn't technically go insane (I made my rolls), but I was getting so freaked out by what was happening that I eventually turned on the other players and begged the monster not to do to me what it was doing to them. I died, but at least it was a clean death at the hands of human beings.