I wouldn't call optimistic, adventurous, original science fiction dead quite yet. Just as an example, there was 2005's Accelerando [accelerando.org], which, with its focus on the Internet and on artificial intelligence, I'd say would be the closest modern equivalent to the space- and robotics-based sci-fi of decades past. Just a new way of exploring a new space and reconsidering the definitions of "human" and "intelligent," is all.
As for science fiction in games, I could only point to a handful of real "hard" science fiction, things whose science are explained and plausible, but just don't exist yet. Obviously, you've got Sid Meier's Alpha Centauri, whose elaborate science-fictitious technology trees are a futurist's dream. There's the upcoming Spore, which appears to make some clear assertions about the nature and destiny of civilization. The Fallout series is well-remembered for its depiction of the future of the real world, and I suppose even Starcraft has got "harder" bits than your run-of-the-mill space opera. I'm trying to think of a game which carries the philosophical (or, at the very least, psychological) component of a work of really noteworthy science fiction, but given the state of storytelling in games I'd suspect such a thing would exist largely by accident.