Zelda OoT: Moving from Kid to Adult Link, realizing that everything had changed.
Zelda MM: When Everything Reset
Xenoblade Chronciles: When I fall of the edge of the starting village and realized the world was fully realized and not just backgrounds, I was damn shocked and started exploring everywhere in the zones I went.
SM64: Not long after I booted the game up, was stunned at what I was playing.
Binary Domain: About 30% through the game it goes from meh to wicked.
World of Warcraft: My first time in Ironforge, everything was a roller-coaster from then on.
Final Fantasy XI: The cities, the world design, the music. Game was amazing from the get go and I loved it until the end of wings of the goddess.
Metal Gear Sold PS1: Psycho Mantis and Meryl Codec on the Cover, that out of the box logic so crazy.
FF7: Forgotten Capital
Castlevania SotN: Reverse Castle
Shadow of the Colossus: Avion and Hydus
Halo: This game was a series of, holyshit moments, I couldn't get over this roller-coaster ride Halo > Silent Cartographer > Assault on the Control Room > 343 Guilty Spark > The Library > Two Betrayals. Those levels are some of the greatest moments in gaming history for me, just amazingly well done.
Demon Souls: Armored Spider
GTA3: When I realized I didn't need a map to navigate the city I learned the streets and were things were by simply remembering where they were and going to them when I needed, it was fantastic design.
Minecraft: When I realized I had spent my entire vacation playing a single game, not much has hooked me quite like that game did since I was a kid.
Super Metriod: When I finished it, couldn't believe how good the ride was.
Chrono Trigger: When I went to school and talked to my friends about finishing it, he said he had just finished it and we talked about the ending only to realize we had gotten to different endings. When we figured out the game had multiple endings we flipped out.
Diablo 2: Was nothing quite like it, similar but the thing blew the competition away once you got into multiplayer.
Body Harvester: Java, once you get there it's amazing. Game was so underrated.
Persona 4 Golden: When your vita goes flat because you didn't notice it tell you it was about to die.
Jedi Knight 2: Physics, shit blew my mind. Picking up a stormtrooper for the first time and tossing him into another was so satisfying.
MGS3: Ocelot
Dues Ex: Paris
Baldurs Gate 2: When I got up to the city and realized I had no idea where to start.
I think that's about it, only times I realized I was playing really amazing games.