Legend of Zelda: Ocarina of Time has the most touching ending I have ever played, and that game is FILLED with epic moments, like watching the sunrise over Lake Hyrule, Link's intimate interactions with several of the characters, especially Saria. It is the greatest game of all time in my opinion.
Call of Duty 4: Modern Warfare contained several moments where I was like "WOAH", such as the point when you are racing to the missile silo, and then you see two nukes get launched. I said out loud "that means trouble" and I just felt like I had to hurry up.
Modern Warfare 2 didn't have quite so many moments. But the end of the Airport scene and the betrayal by Shepherd are particularly memorable moments.
I was always a huge Metroid fan, so playing through the trilogy was always incredible to me. Super Metroid still has one of the best endings of all time.
Dead Space has a head wrecking ending.
Lylat Wars got mentioned, the race to
escape with James Fox.
Portal is filled with hilarious and epic moments, and is so innovative that I rank it among the best games ever made.
I have always been a huge fan of Resident Evil, there are so many things about those games that makes them epic. Resident Evil 1 (the GC remake), Resident Evil 2 and Resident Evil 4 are the best. Excellent characters, and sublime story development for a game whose main premise is mindless zombies.