In terms of single player events, the first time I used the mini-nuke launcher in Fallout 3 felt appropriately badass.
In terms of multiplayer, pretty much everything that happens in Battlefield: Bad Company 2. Blowing the shit out of buildings, taking on tanks with drills, blowing people to hell with a well placed rocket... it all feels awesome. Being chased around by a maniac with a defibrillator is scary as hell, by the way, and probably counts among the bigger 'holy crap' moments I've experienced (I broke down a door, only to find an enemy medic on the other side. Instead of switching weapons, he decided to attack with his defibrillator... which meant I was frantically backpedalling through a confined space trying to reload so that I could kill him. It worked out in my favor, but it was terrifying for a few seconds there).