Yeah if you define doom and gloom as any story in which a character ever dies or retires, you are going to have a hard time finding stories for you. No one is at their peak perpetually forever. It’s natural for stories to reflect that.
I admit I'm a sucker for stories about the Hero in Twilight: ROBIN AND MARIAN, THE LAST JEDI, The Dark Knight Returns, "Solomon Kane's Homecoming," Tennyson's "Ulysses," etc. Always something moving and bittersweet about them.
Tho' much is taken, much abides; and tho'
We are not now that strength which in old days
Moved earth and heaven, that which we are, we are;
One equal temper of heroic hearts,
Made weak by time and fate, but strong in will
To strive, to seek, to find, and not to yield.