OP: If you found Streets of Rage 2 a bit of a downer, you should try and have a look at the fan-based remake (of the entire Streets of Rage series rolled into one). If you haven't, grab it - free download, and basically encompasses the best parts of the whole series, plus remixes of the soundtracks and newly created content and unlockable characters. A true masterpiece.
Also, funny you mention Quake 2, as I had the same experience with you, but when I played Quake 1 again I loved it at and actually had several goes at it, upping difficulty and getting really into it.
Yes, Wing Commander was also a painful thing to fire up after so long, it was THE flight combat sim for me but after the first 5 missions I just had to throw in the towel.
For my own mentions:
*Syndicate: After the FPS reboot came out, I decided to head back and re-experience the tactical magic, and it was a little bit of a let down.
*Resident Evil 4: Hailed as my favorite RE game at the time, and having finished it twice and unlocked crazy amounts of stuff, I went back in and grabbed it on PSN and somehow just couldn't get my head around the controls. How on earth did I stop and aim with the left stick is waaaay beyond me.
*Deus Ex. Anyone who played this game knows what I mean.
I'm about to dive back into my favorite series again, Hitman (starting from Silent Assassin), hopefully I don't get too disappointed.
But on a lighter note, here's a few that I came back to in more recent years and had a freakin' BLAST:
*Jagged Alliance 2 (and mods)
*Fallout Tactics
*Doom 3 (with mods)