For every campaign I run, I try to craft a particular "soundtrack" for it. Meaning, I'll assemble a collection of albums and songs from my larger library that fits the campaign and setting. If you're interested in doing this kind of thing, you really don't need a massive library of music. When I first got started, I only had about 3 or 4 albums that I used all of the time.
If you've never used music before, the best way to get into using music with your games is to put it on in the background. So here's a list of music that might work for you as simple background stuff for your games:
If you just want some exciting music for your games - to play while combat is running, for instance - I can't recommend the Epic Score group enough. Basically these are albums filled with epic-sounding music that you can just put on in the background during a fight and never have to worry about what track you're on. Also, I'd recommend the Savage Tongue by Ars Arcana for the same reason. As an interesting aside, if you listen carefully, you'll note that you've probably heard a lot of Epic Action stuff from movie trailers, commercials, or TV shows.
Also, Epic Action does a lot of work with tense and thrilling music as well. I don't have any of that material, however, so I cannot comment on that.
Follow the links above, and you'll find some great samples of their music.
Mystery, creepiness, and the unknown are strong themes in the 40k universe. After all, there's always the Warp and the constant threat of it.
For my games in Rogue Trader, I've been using a lot of the creepy tracks from Mel Gibson's version of Hamlet. However, that album is out of distribution.
So, if you're interested in picking up some generally creepy and mysterious music, I'll recommend Midnight Syndicate, and the Riven soundtrack. Midnight Syndicate is full of creepy music and sound effects. You can just put it and and let it ride without much worry. The one problem with it, however, is that its mostly gothic horror, which may not mesh well with a space opera.
The Riven soundtrack, however, fits very well with Rogue Trader. And it's readily available anywhere.
It may seem odd, but some of the most used music I have is what I call "downtime music" - music for when the party is just sitting around chatting. It's used quite often, because quite a good amount of time is spent having the Explorers interact with one another. For this, I would highly recommend the score for the HBO series - The Tudors. While the music is meant to be evocative of Renaissance Europe, it fits Rogue Trader because the 40k universe is one with archaic trappings.
Also, there are a number of great music tracks on the Mass Effect 1 and 2 soundtracks. While these tracks are much more evocative of a futuristic and sci-fi setting, you cannot just put in a Mass Effect music album and hit play. Only some of the tracks will end up working for you, so be ready to make a playlist of the tracks you want.