Well the obvious ones for me are Freespace 2 (Most people haven't played it or even heard of it, which is probably why it never got a third installment) But basically it is generally seen as the greatest space combat sim ever. It's the mechwarrior of space sims basically. Confusing at first, but you really get that feeling of immersion as one fighter pilot among many, in the most epic battles with the most epic scale, and the story was fantastic. It sets itself out as this good vs evil plot, but in the background theres always the feeling that nobody really knows if you even have a chance at succeeding, and that there are some people who think that fighting these people is the exact opposite of what we should be doing. There are these rebels that you fight in the earlier levels, who are led by a man who plans to talk and ally himself with the evil aliens, and maybe he's got the right idea. Maybe you're on the wrong side.
Sadly it never got to expand on that story, and though Freespace 2 does close up really well, there's still a lot of questions, rather like if Half Life 2 was the final half life.
Also one interesting thing about it is that it had a weaving mission structure. You could fail some objectives, and sometimes even entire missions and keep going, though you would miss out on some paths you could go down, such as promotions to higher ranks, or even as tester of experimental technology.
The good thing is that that the source code for the Freespace 2 engine was released, so now modders have gone crazy with it. I wanna go back and play it, but that takes quite the commitment. I just dont have the time.
Then there's Dungeon Keeper 2.
Some people probably know all about this. One of the few games to come out of Bullfrog's acquisition by EA back when EA were the major evil corporation. Despite the conditions they were forced under, Dungeon Keeper was just a spectacular management game. You manage a dungeon, attract monsters to live in your dungeon, and fight heroes who invade your dungeon. You can also use spells of your own. My particular favourite was the possession spell, where you can go into the mind of one of you creatures and use them to fight or explore. There's also traps and mechanisms that you can make, and you can capture heroes and either torture them to join your side, or let them rot in prison to become an army of skeletons who dont need to eat or sleep, but who are also weak and don't heal.
And ofcourse there's "horny" the Horned Reaper. There's a statue of him at my local megabowl. In the first one he would live in your dungeon, and would sometimes get in fights and kill your creatures. In the second one, you gain his trust over the levels and summon him as a kind of super weapon.
The second game was a massive lead up to the third which was supposed to be set in the land of heroes. It was never made as Bullfrog broke up under the weight of EA. Most went on to create Lionhead studios, and others created Mucky Foot. Both made Bullfrog style games, but neither managed the same quality. Mucky Foot died after two or three games.
Those that stayed at EA became EA UK, which also died and moved to Guildford.
It should be noted, Dungeon Keeper is getting a new game, but it's a chinese MMO with little to no connection to Dungeon Keeper at all.
There's also the Dungeon Keeper alike, called Dungeons, which looks a LOT more interesting. Though it might end up just like all those X-COM alikes, where they almost manage it but not quite.