Eternal Darkness is probably my favorite.
Silent Hill 3 comes in a close second, with Silent Hill 2 trailing. See, SH2 pissed me off a lot--the story was incredible, but I hated the gameplay. SH3 was more rounded in that aspect, I think.
Though, Fatal Frame 2 is the only game I literally had to stop playing. I had my headphones on and I heard pretty much everything, and I had to quit the game because I was too freaked out.
I never played it after that, but that was because I hated the game when it wasn't being scary and it was being boring. So I don't know if that's good or bad.