Has too many to count, but a particularly cruel one happens to Tony Almeida on Day 3. After an agonizing 4 or 5 hours of knowing his wife Michelle is trapped in the hotel where a fatal virus has been released, he learns she's immune and breaks down crying in relief. As Michelle is on her way out of the quarantine zone she's taken hostage by the Big Bad, who immediately forces Tony into a Sadistic Choice between betraying all of his colleagues or letting his wife die after all.
Day 3? If anything you'd think the first finale had to be the most infamous/cruel one in the series. Think about it: After enduring hell for the past 24 hours, Jack has finally killed the Big Bad of the season, gotten Nina Meyers arrested, and reunited with his daughter Kim. Then he heads into the room where his wife is being held... and breaks down upon discovering that Nina had shot her minutes before and she's bled to death. No, Jack. There isn't going to be any happy ending for you.
Day 5: Lynn MacGill seems to think he might not have made a Heroic Sacrifice after all after he stops the gas from killing everyone in the building, but it just took the gas a few seconds to reach him, and he and the man guarding him die horribly.
Day 5 again: Evelyn Martin and her daughter are rescued by Jack, only to be murdered offscreen a short while later.
Day 8: CTU's attempt to rescue President Hassan falls through when they find out the video feed was prerecorded and he had been dead for some time.
Day 8 again: Jack decides he's done with CTU and he and Renee Walker finally resolve some serious UST. Renee gets shot in the stomach and dies. Jack doesn't take it well, to say the least. Jack's despairs all the more because Renee's killer could have shot her in the head, but went for the stomach just so that she would die slowly and painfully - and from an out of universe perspective, to give the audience a glimmer of hope that she would survive.
Wesley's final battle in the finale might be another example; the demon has him beaten with magic, so Wes resorts to a knife. The demon then blocks him, grabs a bigger knife, and proceeds to fatally wound Wes.
There's a great subversion when the demon tries it again. Unfortunately for him Illyria is quite able to kill him with the one free punch he offers her.
And let's not forgot Wesley's hilarious "I think we're winning!" in Over the Rainbow. Cut to shot of the good guys tied up.
The second fight with The Beast has two. The first is when Wesley's shotgun blasts bring it to its knees, with him advancing on it firing from closer and closer - until he gets within range, at which point it grabs him, hurls him into a wall, and stands up as though its apparent injury was a just an act. The second is when Angel puts his Game Face on and goes in for a rematch. He also manages to knock The Beast to its knees, produces a stake, and stabs it towards The Beast's eyes, the only seemingly vulnerable part of it. He stops an inch away, and the camera reveals The Beast had caught his wrist. Then it stabs him in the neck with his stake and hurls him off the skyscraper.
Battlestar Galactica (Reimagined): The midseason finale of Season Four. The humans and Cylons have put aside their differences and found Earth together. Cue exulting music. Oh wait, Earth is a nuclear wasteland and has been for two thousand years.
Blackadder: In the Blackadder Goes Forth'' finale, there's a Hope Spot when they're in the trench ready to go over the top and the guns go silent. Then:
Darling: Thank God — we lived through it — the Great War, 1914 to 1917...
Blake's 7: The entire final episode. After two seasons of searching, the loss of virtually all the original crew, the Liberator, and most everything else, the crew finally locate Blake on Gauda Prime. But put Blake and Avon in the same room, and all goes to hell in short order.
Cory: You just love dangling that little string of hope in front of us and yanking it away, don't you?
Mr. Feeny: I had a cat.
Later Played for Drama in one season 6 episode. Shawn's father is back in town, suffers a heart attack and after much drama and anguish the man promises his boys that he'll stick around from now on and they'll be a family. And then he dies.
To be fair, he still comes back to keep his promise.
Buffy the Vampire Slayer: The episode "Help", where Cassie (teenage girl who has had a premonition of her own untimely death) is saved by Buffy from being killed by a demon... but then a lethal trap goes off... and Buffy saves her just in time for a heart attack to kill Cassie.
CSI: Crime Scene Investigation: During the multi-part episode of "Grave Danger", there's a really cruel and clever one of these. At one point, the CSI's think they've found where Nick has been Buried Alive. They start digging, and at exactly the same time, Nick can hear a scraping sound from outside the box. The team get really excited when they realise that the thing they're digging up a plexiglass box, and Nick starts banging on the box and calling out to them, clearly ecstatic that he's about to be rescued. The box the CSI's dig up contains a dead dog, and the scraping sound Nick was hearing was actually the box cracking, as he'd weakened it by shooting out the light.
The series 4 finale, the Daleks are defeated yet again, and the Doctor finds himself surrounded by all of his companions and friends from the start of the new series including Rose, who he thought he would never see again. Everything is shiny and full of laughs for the next few minutes until half of them go home, Rose is left in a parallel dimension with the Doctor's clone, and Donna gets brain-wiped. The last few scenes show the Doctor cold and wet, staring ahead blankly in the TARDIS, completely alone again.
"Voyage of the Damned" has another powerful one. Astrid has driven herself off a precipice into the ship's reactor in order to off the Big Bad. As the Doctor returns to the other survivors, he is reminded that she was wearing a teleport bracelet with a system for rescuing passengers who run into trouble with it on. So he attempts to recall her... only to find that the system does not have enough power, and all that's left of her is constituent particles in her image, which he has to disperse. Hell of a Tear Jerker.
The End of Time is very mean about its Hope Spots. The Doctor has just saved the Earth and possibly the universe from the opening of the sealed time bubble and the return of Gallifrey, and he's still alive, despite thinking that The Master's return was what was supposed to have killed him. Then he hears a sound...four knocks, from Wilfred, on the inside of the flood chamber for the radiation, which The Doctor will activate if he lets Wilfred out, spilling the radiation on him...the entire rest of the special is a class one Tear Jerker through and through.
In Fear Her, all the drawings have returned to life - except the Doctor. Then the other drawing comes to life, and it's an Oh Crap moment.
A particularly nasty and heart-wrenching example from "The Angels Take Manhattan"... Rory and Amy have jumped to their supposed deaths to create a temporal paradox to poison the Weeping Angels' food supply. The paradox puts them back where they started the adventure, ready to head out with The Doctor and River on a family voyage, until Rory is distracted by his own gravestone and is sent back in time by a weeping angel and Amy goes with him. And thanks to the paradox, the TARDIS can't go their time/place, so the Doctor and River will never see them again.
In A Good Man Goes to War, Rory saves his and Amy's daughter from being kidnapped. Amy takes the baby and hides, while Rory, the Doctor, and the various aliens he recruited to help out form a line of defense to stop the last of the troops coming to get little Melody Pond. It turns out that the baby Amy was holding was just a "Flesh" replica, and the real baby had been kidnapped long ago. Yeah...
Dune: The 2000 miniseries has a beautifully dark and very justified one near the end of the final chapter, where Rabban, a brutal and savage oppresor of the people of Arrakis, find himself surrounded by the very people he had been oppresing. The Hope Spot kicks in when he sees Stilgar, the leader of the Fremen rebellion, bearing a rifle, and seeming to offer Rabban the hope of a quick clean death by gunshot... only for him to walk away, leaving him to the knives of a few hundred people who have no interest whatsoever in giving him a quick clean death.
Early Edition: Gary enters a hospital where some beat-up teen is being treated. Gary's newspaper tells him that the teen is slated to die. He tells the reception at the hospital about the teen who's about to die, but the reception insists that he's making a recovery. Suddenly, the teen indeed kicks the bucket, and the next scene is his funeral.
Firefly: The opening sequence to the first premiere. Mal charges out of the bunker to shoot down a flyer that was keeping away the reinforcements. He returns triumphant... and the reinforcements don't come anyway, as Command has surrendered the battle — and to make things worse, the reinforcements that do arrive proceed to rain fire upon Serenity Valley, killing everyone except Mal and Zoe.
FlashForward (2009): The episode after Al Gough screws destiny begins with a montage of the world and the main characters being optimistic after learning that the future can be changed.
Game of Thrones: Being based on A Song of Ice and Fire, it is no surprise that the show does this a lot, too. An altered scene that adds an additional Hope Spot on top of those already in the books occurs in the middle of Season 2. Catelyn Stark manages to secure an alliance for her son Robb with Renly Baratheon, the sanest and most powerful claimant to the Iron Throne who also hates the Lannisters, in return for a token show of submission to Renly once the war is over. No sooner has Renly agreed to this before he is slain by a monstrous Living Shadow created by Melisandre, shattering his coalition which results in the Stormlands bannermen joining Stannis's cause, and the Tyrells eventually switch sides to the Lannisters.
In season 4 Tyrion has been accused of murdering Joffrey. The people still loyal to him are powerless to stop his conviction so Tyrion chooses Trial by Combat as a final Take That to his father. It looks like Tyrion will be killed by The Mountain in the duel when Oberyn Martell offers to be Tyrion's champion. During the duel it soon becomes clear that The Mountain is losing and with his death Tyrion will be exonerated. Then right before delivering the final blow, Oberyn gets careless and The Mountain grabs him and crashes his skull with his bare hands. With his champion dead, Tyrion is pronounced guilty and sentenced to death.
Grey's Anatomy: One episode had a main character finally wake up from being in a coma for a few months. Everyone is happy, though of them note that it's not unheard of for people in comas to wake up and have one final burst of energy ("the surge") before dying. Everyone denies it's the surge, saying he's going to be fine, because they don't want it to be true, though the patient knows he is going to die and plans accordingly. To the viewers, it's a Foregone Conclusion, since he died the episode before after slipping back into a coma and being taken off of life support after 30 days (as he requested).
In the episode "Six Months Earlier", Hiro travels back in time to rescue Charlie from Sylar. It turns out she would have died of a blood clot in her brain anyway. Then Hiro accidentally teleports to Japan and can't get back until she's already dead.
Subverted Trope when in season 4 Hiro goes back again, and this time convinces Sylar to remove the brain defect from Charlie then unsubverted when later that episode, after being cured, Charlie is kidnapped by the new big bad, Samuel, before Hiro and her can run off for their happy ending.
Kamen Rider Fourze: The climax of episode 31: the Kamen Rider Club instigates a Misfit Mobilization Moment among the Subaroboshi students, turning them against the Aries Zodiarts! Gentarou/Fourze has figured out the limits of Aries' power, allowing him to get the upper hand. Aries is about to be defeated, until Meteor arrives, reveals he made a contract with Aries, and then proceeds to kill Fourze.
We spend most of the episode "Greatest Hits" expecting Charlie to drown in a flooded underwater station at the end. When he gets down to the station, it's not so much flooded, and Charlie doesn't die. Then in the next episode, Mikhail does flood the station, and Charlie dies in a poignant Heroic Sacrifice.
There's also the bizarre double case in which Locke, after causing Boone's death, goes to the hatch and begins banging on the door and screaming. This causes Desmond, who was in the hatch, to abandon his suicide attempt because he thought Locke was his new button pressing partner. Desmond shines a light up through the hatch door just as Locke is screaming at the island to give him a sign. Of course, Locke turns out to not be Desmond's new partner and, when he finally gets into the hatch, the computer winds up getting shot, which causes Desmond to both panic and go into a deeper depression than before. Locke goes on to destroy the computer that gave him so much hope and purpose after he loses faith it. Later on, Locke is killed, taking away his "special" status and making his moment of hope meaningless.
Not to mention Locke's apparent resurrection by the Island after the Ajira crash. Instead of rematerializing as Ben's personal chew toy, he shows some backbone and bosses Ben around, seemingly living up to the "special" status he'd hoped for. Unfortunately, New and Improved Locke is just the Man in Black in disguise, meaning the real, unspecial John Locke really did die tragically a half-season earlier.
Mighty Morphin' Power Rangers: "Master Vile and the Metallic Armor part 2". Tommy and Kat have nabbed the Zeo Crystal and recovered the Falconzord. They blast Globbor, then combine with the Ninja Megazord to pound him. They seem to have won... then Globbor gets up and turns out to be just fine. And worse still, Master Vile reveals that Ninjor is absorbing the damage. Globbor defeats both the Ninja and Shogun Megazords, which are then teleported to a distant planet with the Zeo Crystal now linked to Master Vile. Globbor also drains the Rangers' energy, weakening them severely.
An earlier example combines this with Like You Would Really Do It: Rita has created a candle that will destroy the Green Ranger's powers when it burns down all the way. Jason fights his way to the candle, but must leave before he can extinguish it because the rest of the team is about to be defeated in battle. End result: Tommy loses the Green Ranger powers and the team loses a valuable ally until the White Ranger powers are created.
NCIS: Kate's death. She's shot in the chest, but was wearing a bulletproof vest. She gets up, and is killed mid-sentence. Sentence being? "I never thought I'd live-" She's shot in the head, by the way. No way she's surviving that.
NCIS: Los Angeles: This LA counterpart to NCIS has one with Dom. Kidnapped by terrorists and missing several months, the team discovers he's in LA and launch a rescue mission. Sam finds him on the roof (Dom admitting he always knew they'd find him), only for Dom to get gunned down moments later.
New Tricks: Rare example of a villain getting this from the pilot episode; a vicious, psychotic gangster has been released from jail following an appeal of his conviction for the murder of a barmaid two decades beforehand. He and his friends are celebrating in a posh restaurant when the main characters enter the restaurant to officially and publicly announce that DNA evidence has cleared him of the murder he was convicted for. The entire room begins to celebrate... moments before more police officers flood in to arrest him for another murder two decades ago that same DNA evidence can now conclusively prove he did do. Oh, and turned out his wife murdered the barmaid under the mistaken impression she was having an affair with her husband.
Beecher tries to go on a Roaring Rampage of Revenge following seeing Keller, the guy he fell in love with, working with Schillinger his enemy but in a tragic Hope Spot fails and ultimately gets his arms and legs broken by Schillinger and Keller.
A worse one comes in the end of season 4. Beecher is paroled thanks to an attorney he fell in love with, says goodbye to Oz, gets finally out...and wakes up. It was All Just a Dream, and in reality, Beecher's parole is refused, and he is forced to stay in jail. The show's future was uncertain at the time; in the event of the show's cancellation, the scene would not have turned out to be a dream.
It gets even worse than that for Beecher. In season 6 he actually does get paroled, but after about six weeks, Keller tricks him into violating his parole and having to serve the remainder of his sentence.
Revolution: In episode 11, Danny dies taking out Monroe's only power amplifier with him. By doing this, he effectively brought Monroe back to square one. Randall shows up on Monroe's doorstep not too long after, and not only offers him more power amplifiers but soldiers, weapons, and a full team of scientists.
The series two finale is just one Hope Spot after another, culminating in Robin and the outlaws tied up to die of exposure in the desert. Marian arrives on the horizon to the joy of everyone, only for her to be accompanied by the Sheriff and tied up with them. A second miracle arrives in the form of Carter, who frees everyone, only for the Sheriff to kill Carter and Guy to murder Marian at the climax of the episode.
The two-part Grand Finale of same show can be summed up as "Every time things seem to be going right, somebody dies". The Sheriff's army make their presence known by dumping Allan a Dale's body at the castle gates just when it seems like the battle's over; when it seems like the blocked escape tunnel might be cleared allowing everyone to escape, Gisborne gets killed and Robin is lethally poisoned. The other outlaws don't find out about this last part until the castle and the Sheriff's entire army has been killed.
Samurai Sentai Shinkenger has one in one of the last episodes: the Shinkengers have an all out battle against Dokoku to buy Kaoru the time she needs to use the sealing character that will lock the Big Bad away forever. Just as the team gets beaten to within an inch of their lives, she finishes and the seal hits Dokoku full force...only for him to walk out of the explosion with only minor damage, as he found a way to negate the effects of the character. The episode ends with Kaoru too injured to continue fighting and the others desperately searching for a solution.
The Sarah Jane Adventures: In The Temptation of Sarah Jane Smith we see present day London in ruins and Sarah Jane and Luke stuck in the past with no idea how to defeat the Trickster without...bad things happening to Sarah Jane's parents. Cut to the image of a Blue Police Box as the Doctor's music plays. It turns out to be just a policeman.
The Star Trek: Deep Space Nine episode, "The Sound of Her Voice" is one big Hope Spot. The crew of the Defiant receives a distress signal from a shipwrecked Starfleet officer, who is slowly suffocating on a not-quite-habitable planet. As the Defiant rushes to her aid, the senior staff take turns talking to her over the radio. The rescue party seems poised to reach her in the nick of time... only to discover that an ill-explained time displacement was in effect, and she's actually been dead for years.
The first half of the finale of Season Two has Sam and Dean separated and when they finally meet each other, Sam's rival stabs him in the back and he dies in his brother's arms.
Mystery Spot also had one of these. It's a Wednesday, Sam thinks they've beaten the trickster but Dean gets shot by a mugger, is dead before Sam even gets there and Sam can't even wake up this time.
As Sam pretty much says in Devil's Trap, they're still alive (although Dean just barely), they've still got the Colt and we would love to believe him if it weren't for the fact that, in a second, a giant truck will smash into their car on purpose, leaving all three of them bloody and unconscious. What a fun way to end the season!
Every season finale. No Rest for the Wicked has them just about to murder Lilith— the entire episode is one giant Hope Spot— and Lilith turns out to be possessing Ruby, and Dean goes to Hell anyways. And then Lucifer Rising: Dean gets to Sam with some help from Castiel, Sam hears him calling out and puppyfaces "Dean?", and then Lilith hits his Berserk Button and he kills her, destroying the final seal and releasing Lucifer from his prison.
Veronica Mars: It has one. Logan's mother dies, and he is convinced that she is still alive. He asks Veronica to find her for him, and she tries to do so ( encouraged by the knowledge that the woman who supposedly witnessed the suicide was a liar) until Weevil finds a freshman with a tape that shows, amongst other pointless things, something dropping off the bridge at around the time that his mother jumped. Logan then gives up until Veronica gets a hit on one of his mother's credit cards- someone used one. They go to where it was used and find a woman there. Logan tentatively asks 'Mom?' only to have the woman turn around to be revealed as... his sister, Trina. Makes you want to cry. Supposedly her death was purposefully left ambiguous in case the writers wanted to bring her back, so does that count as only a half-futile hope spot?
The Young Ones: The final episode contains a two-fer; the boys have escaped their confining, limited lifestyles and have their whole lives ahead of them, full of freedom and hope... only for them to accidentally drive through a billboard of Cliff Richard and over a massive cliff. Then, at the very bottom of the cliff, from within the battered, smashed-up bus we hear them say "Phew! That was close!"... and the bus explodes in a massive fireball from which nothing could survive.
Air Crash Investigation: Air Canada Flight 797 managed to safely land after a severe on-board fire, and it seemed that the passengers would all make it off the plane... until the plane's doors were opened and a flashover occurred, which incinerated the interior and killed 23 people
Another Star Trek example in Star Trek: The Next Generation, in arguably the Trope Codifier for the cliffhanger season finale, "Best of Both Worlds Part 1." After the Borg abduct and assimilate Picard, Riker gives the order to fire the superweapon improvised from the main deflector dish. The music swells and Enterprise opens fire. Only for Part 2 to reveal the Borg have already adapted for that weakness, because they learned of it from Picard. The Borg No Sell the attack, Enterprise is temporarily disabled, and the cube heads to Wolf 359.
Two seasons earlier, Picard had one in "Conspiracy" when he was trying to escape from a room of possessed admirals and ran into Riker, only to discover that he was possessed, too. Subverted, though, when Riker revealed that he was only acting possessed so he could get close enough to shoot the admirals.