Session 9. Yes, I like that movie too. It wasn't scary to me, but I enjoyed the story.
The last time I was really scared by anything, it was the video game Silent Hill 2. It has some of the best music and sound effects, imo. It isn't about gore or shooting enemies so much as the otherworldly atmosphere of the town and its decrepit buildings and streets. And it actually has a good story! I've played it through many times, but it doesn't affect me like it did that first time. None of the sequels ever quite matched it either.
However, I agree with Terry's comment above. I no longer believe in the supernatural, so I just don't find horror movies and games scary like I used to.