Hmmm, a couple of games. Half Life 1 and 2, music that pérfectly fits with the thing you're doing, like that chopper chase with the airboat, or the spooky Ravenholm entry music. It's the turbo-boost for the adrenaline pump

Then we have Halo, also a perfect atmosphere setter, really makes it feel epic sometimes. There is also WoW, especially WotLK, I kept being blown away by there theme and ambient music, pérfectly setting the atmosphere in any given zone.
A special mention is given to Rayman 3 Hoodlum Havoc, for having the most epic minigame/bonus stage in any game to date, seen in this video [http://www.youtube.com/watch?v=iTl4_qTiKVA&feature=related] when you zap to 1:59. I mean damn, that's just pure funk man, imagine playing that level with a magic mushroom, wooooohoooo! 3:57 Is spaaaacyyyyyy!