In Homeworld Cataclysm, at the end of the mission where you rescue the Faal Corum (I think). I sat for well over an hour waiting for my resource gatherers, just watching the flashes in the darkly beautiful nebula behind my ship and, crucially, with Track 11 of the soundtrack playing in the background:
created a sense of the hugeness of space unlike any I've felt before or since.
In System Shock 2, my actual favorite game, it'd have to be the first time I played the demo and killed the first hybrid with the wrench. I sat in the corner for half an hour looking down the two hallways, too scared for my back to leave the wall.
Half Life 2, my newest favorite game: pretty much the whole first half of the game, but particularly the bit where I stepped out of the train station to see the citadel. Brilliantly understated, no more evil-looking than any human skyscraper but right away you know what it stands for. Second moment would've been when I was in the canals, hearing the Overwatch voice straight out of Orwell and looking up at apartment buildings that look like the ones outside my real-life window.