When I was little I would say it was facing The Butcher in Diablo.
I remember I once waited until I had been through the entire game except for diablo himself before I finally went back to face the butcher, beat him in two hits, but thats because i was much stronger than i was meant to be when facing him

.
the time before that though ((begin story time)) :
I go up to the door fearfully, i knew what awaited me inside. After working up the courage, I click on it, and the butcher's voice cries out -
"Agghhh!
Fresh Meat...".
My courage evaporating in an instant I frantically close the door again, running back and waiting for him to come through... except he doesn't.
Ok, can't open doors, good. Again I build up the courage to open that door, in that split second I see the butcher inside, he's making his way around the corpses, towards me. I run for dear life, escaping, but this time leaving the door open.
Satisfied i have outrun him, I circle around back to the middle of the level where his room is.
The butcher must be somewhere in the eastern part of the level (since I had run that way when he was chasing).
Perfect! I'm safe for now. I walk around the butchers room just be be sure, but he's gone. curious at the novelty of having the butchers room with no butcher to guard it, I decide to go check it out
The doorway to the room was on the eastern side, so to avoid him potentially seeing me and coming back, I walk just inside the entrace and close the door to the butcher's room behind me. neat, I'm in his room. I walk out into the middle.
"AGGHH! FRESH MEAT!"
The butcher is right there, running at me from the corner of the room. He was waiting in the room all along, up against the eastern wall, hiding behind a corpse so that neither of us saw each other untill i walked into the middle there. He hadn't chased me out of the room, i'd run off to quickly, and now i'd just closed myself in the room with him.
Scared the crap out of me man XD can you blame me for avoiding him on that next play through?
------
As for modern day... I don't know, maybe one of the bits from Doom 3. They could be pretty frightening. ((after playing doom 3, half life 2's ravenholm really wasn't very scary for me))