Gauntlet Legends for the N64.
Shadow Hearts: Covenant for the PS2.
Persona 4 for the PS2.
Earthbound or Digital Devil Story on the SNES. They both tickle the same spot in my brain.
Deadly Premonition for the Xbox 360. 2010.
STALKER: Call of Pripyat - PC. 2009? Yeah.
Hitman: Blood Money - PC. 2004, I believe. Maybe '05.
Max Payne 2 - Xbox. 2003, then.
Team Fortress 2, for PC of course. Released in 2007 and looks to last a lot longer.
and Mushihimesama Futari for the Xbox 360.
EDIT - Special Mention. One of the most memorable, but most fucked to play was Robot Alchemic Drive. It felt exactly like controlling a giant robot. I loved it so, even though I was never able to beat it.