I'll also agree with the Sonic 2 drowning noise. Damn you chemical factory!
When I was a kid, the monster at the end of skifree (yeah, that's right, skifree) scared me like nothing else. I played that game so much, and I'd expect him every time, but he'd get me at some moment I wasn't ready for anyways. That had to be the first instance of horror I've ever experienced, and it was enough to give me nightmares.
Other than that, the entire nightime part of Gears of War where you have to get the vehicle was intense, especially in co-op. Those swarming locusts that kill the soldier just to show you what happens when you step in the dark, then the part with the propane tanks, goddamn.
And since it's fresh in my memory, running out of items in Dead Rising. Especially in the maintenance tunnels. At that point, the only thing you can do is run like hell, and you actually feel scared doing it. The first time in the entrance plaza is very disconcerting as well. When the zombies break in and all you see is "person X IS DEAD" in giant red letters, and little clumps of zombies swarming people. Repeat that about 13 times in about 30 seconds while you're desperately running for the stairs, and that first time you get grabbed and bitten (it definitely happens the first time through) is some of the scariest stuff ever.