Far Cry 2. I loved every aspect of that game, but the endings are so depressing that I pretty much never played it again.
Gun also wrapped up a bit too quickly for my taste but it still rocked out loud.
I think the worst offender, however, is P.N.03. Great little arcade-style shooter, but the story (such as it is) is done so badly especially near the end that it made me wonder why they tried to give it much of a story to begin with. Tons of games are fine without them (Painkiller, Burnout, Super smash Bros)