In STALKER, sitting around the campfire whith a bunch of fellow STALKERs and one whips out a guitar and begins to play. It is one of the few moments that you feel safe in that game.
The first time you blow an enemy apart with a shotgun in Gears Of War. Priceless.
As previously said, the climactic battle at the end of Mass Effect.
Sticking with Mass Effect, I'll also have to say the first time you see the Citadel, as you are flying in acquiring permission to land.
Sticking with Bioware (my favorite developer), in KOTOR the first time you see the Star Forge and realize how much it kicks the Death Star's ass.