I wear my nerd tag proudly, I have a hefty list. If you haven't played these games and don't want spoilers, I'll white out the spoilers.
Final Fantasy VI
The Opera Scene. I don't know what it is about this, but of all the great moments in what I consider to be a top 5 RPG of all time... the opera scene sticks out to me.
When Crono get zapped by Lavos, the moment is somewhat dulled by the fact that you can bring him back to life.
Final Fantasy VII
My jaw dropped to the floor when Aries died. It wasn't the first major character in a game to get killed off, but it was so unexpected. Very powerful scene.
Final Fantasy X
The final battle with Jecht/Sin. The dialogue before the fight really was moving.
I was playing an evil playthough, getting near the end of the game... and Kessler who is the main bad guy has the main characters girl held hostage. He gives you a choice to save her, or save a bunch of doctors and do a lot more good for everyone as a whole. Hell if I was going to let him kill my girl. But Trish wasn't where he said she would be, she was with all the doctors. I was playing an evil playthrough and Trish's dying words were that she was ashamed of what I had become. I did nothing but good missions for the rest of the game.
Mass Effect 2
Through the Omega 4 relay... nuff said. Pure epic awesomeness.
Dragon Age Origins
The final march on Demerim, seeing the whole army that you assemble coming together to march on the capital which is already overwhelmed by the enemy. The dialogue, everything about it was just amazing.
I know there is more, Ive been a gaming nerd as long as I can remember... Ill post some more when I think of them.