The only scary moment I've experienced in a video game was in Max Payne for the PlayStation 2.

There's a thin path of blood. If you fall off the path, Max Payne will fall into a black space with a weird scream. There's a lullaby song playing, and there's blood on the walls of his child's room.

I avoided that level since then.