I don't know how many people are going to get the reference, but this very much reminds me of my favorite book series, The Dresden Files.
Every book has our hero, Harry Dresden (professional Wizard living in Chicago), up against some sort of monster of the week or cosmic horror threatening to tear reality in half (because hey, somebody's gotta do it), and Dresden steps up to fight the baddie. Except, most of the time, the bad guy is a whole lot stronger than him and Dresden gets by on sheer luck and relying on allies along with skill and tricky ploys.
In every single book, Harry gets beat to shit and back. It's kind of one of the defining things about the series, and makes him a good character. By the end of each book, he's been beaten to near death (sometimes mentally and emotionally as well as physically), is running low on power with which to use any magic, and might actually be dying. But damn it, he always manages to fight as long as possible, even when it might (or definately will) get him killed. A good series, by the way, definately recommend it.
In the gaming world, you can kind of (vaguely) look at Metal Gear Solid 4 for this. Snake, the hero that's been whispered about for years, is, well, old. His back acts up if he runs for too long, smoking makes him cough something terrible, and he's on the verge of death, for the most part. Granted the game mechanics still allow you to kill armies by yourself as long as you keep Psyche up, but the idea and possiblity was there. The final act actually played this out beautifully (especially in that hallway that I'll try not to spoil here), and the final fight at the end really gave you the feeling of a soldier on his last legs finishing his last mission.
Granted, they could have played with it more in MGS, but I definately think it could work in an RPG setting, where the rigors and confrontations of your journey leave your worn out and weak near the end, at the point where you'd want to be your strongest. I'd think that it would make victory that much sweeter.