Eternal Darkness: Sanity's Requiem - When my game gave me the blue screen of death. I was playing at 2 in the morning and shouted out loud which didn't make my neighbors in the apartment next door too happy.
Star Wars: Knights of the Old Republic - When Darth Revan was unmasked, I was literally floored. I tried to load it again just so I could watch it, but ended up saving over my previous file.
Legend of Zelda: Windwaker - Defeating the final boss. Yeah I get it, we've been hacking and slashing for 25+ years. I just never expected to ever see such a direct blow before.
The very beginning and endings to Silent Hill: Shattered Memories. A lot of people give that entry grief because it doesn't have the monsters or other things synonymous with Silent Hill. Here's what the game did deliver on though, Emotional and Psychological levels. The beginning and endings are told to the player though home movies. Seeing video taped evidence of the picturesque family devolving into a fractured broken home filled with abuse, alcoholism, and adultery really tugs on your heart strings.
There's probably more moments that had a significant impact on me, but these are the ones that stick out the most.
I'm typically inactive between Spring and Fall. I'll be on as often as time allows.