Sometimes that's how a story should end. Stories about the fall of a character really must end in tragedy. Some stories about redemption must as well.
Macbeth, for instance, is about a man who allows a prophecy of his greatness to get to his head and does terrible things to ensure its fulfillment; he dies at the end due to his own failure to control his ambition (as well as that of his wife). That's why it's a tragedy.
Consider also the story of Anakin Skywalker; he didn't fall to the dark side and become Darth Vader randomly. That tragedy happened slowly, over time, as a result of his passions, his strong will, and his inability to keep those in check when dealing with those he loves. This also showcases the redemption; he, as Darth Vader, had gone way too far to survive a choice to return to the light. He gives his life to right his wrongs and save his son, freeing the galaxy of Imperial oppression through his actions. This is the ultimate sacrifice, the most complete way to take responsibility for one's actions and accept the punishment while also setting things right.
To take another more recent example, Paranormal Activity. The two protagonists are eventually taken by the demon because of both of their inability to deal with it. Micah is just generally being a jerk and not taking Katie's fears and experiences seriously, and Katie lets her fear control her and fails to take control of her own reactions. These failures to deal with the situation only exacerbate things and, eventually, lead to the death of both characters (in some endings, at least; I suppose considering the sequel the canonical ending has Katie remain alive and possessed).
But you are right in that sometimes it just doesn't work. For instance, Paranormal Activity 2. It followed the rather awful trend in horror films (seen also in Silent Hill, Nightmare on Elm Street, etc.) of wrapping everything up, having the main characters emerge victorious from their struggle... only to be defeated by a random force completely out of their control. Sometimes it's not even explained; there's just some random, unexplained thing that gives the story a bad ending despite the enemy having been decisively defeated. That's stupid. It's infuriating. And, rather than the endings of things like Star Wars, Macbeth, and the first Paranormal Activity, the death (or whatever else) means absolutely nothing; if there's a point to the story, this cop-out attempt at a scary ending makes the story about how nothing you do will ever amount to anything because your fate is entirely out of your hands. Nothing against Nihilism, but I see no point in telling a story like that.
