The Starsailor song from MGS3 is exactly the sort of song I'm talking about. I finished that game despite not liking it, and that song felt like a really good fit given how the game ended and all. (Also, even though my liking for MGS dropped off pretty hard after 2, that main theme still gets me every time!)