In The Lost Heir, in the last scene of the last book you can be completely consumed by your demonic side and wind up abandoning all your friends, your lover and your child alongside your entire kingdom, likely for most of them to die. You can be a martyr and die for them all too, though that’s more bittersweet than failure.
In Heroes of Myth, you can be thrown in prison, die, etc. All kinds of horrible fates.
In Créme de la Créme, you can be kidnapped, and though I haven’t tried it yet, I’m pretty sure you can fail to escape the prison camp you’re sent to.
In Community College Hero, you can fail to save two characters, your teacher and one of your friends. Whether you like those characters or not, as a hero, that’s a massive failure.
In the Versus series, you can get voted to die in the arena if you fail at your objectives.
In The Last Wizard, you can lose the final battle–and the final stronghold–against a tyrannical empire, and thus doom that entire world for however long. You can also die, be held captive, get trapped basically as a slave in a realm you despise (depending on your perspective by the end/choices), etc.
In the Evertree Saga, you can be driven to murder and/or lose everyone you love. I haven’t tested it out, so I don’t know if you can doom the town to eternal torture in the sequel, but quite possibly as you seem to be able to run away at a pivotal moment. You can also die I’m pretty sure.
There’s a lot of other series and individual games I’ve heard have tragic or spectacularly failing routes as well; however, I can’t comment on those as I haven’t finished first playthroughs yet.