Please don't list this on a work's page as a trope. Examples can go on the work's YMMV tab.
Tear Jerker: Stargate SG-1
The look on Daniels face in "Children of the Gods", when he realizes that Shar'e has been taken as a host.
Subverted in the third episode — Charles Kawalsky, who the viewer would have thought was going to be a major character, is in great pain in a hospital bed. The music takes a distinctly mournful tone. Jack, with the saddest, most genuine look of sorrow on his face asks him, "If you don't make it... can I have your stereo?" Kawalsky, and the viewer, choke out laughter.
Then the actual death of Kawalsky, a character who survived the film dies in such a heart-wrenchingly senseless way.
Even worse when you realize that Kawalsky was probably who Jack named his son after...
The ending of "Fire and Water", where Nem uses his memory machine on Daniel Jackson to find out the fate of Omoroca (his mate) from ancient Babylonian texts.
Daniel: And in that place there was Omoroca, a woman who came forth from the heavenly egg, who walked among men by day. But at night she would retreat to the sea to sleep — one of the beings called Ohnes. The godBelus came down onto Babylon unto the place of Omoroca... and cut the woman asunder!"
This scene is one of the rare moments where a Big "NO!"works.
Daniel Jackson's funeral.
Sha're's death at the hands of Teal'c is uber heart-wrenching to watch.
The seventh-season episode "Heroes".
The death of Doctor Janet Fraiser, that is all.
The second half of the "Heroes" two-parter. The whole episode revolves around the death of one character. The audience is led to believe it's one character. At the end, it is shown, in startlingly undramatic and completely heart-shattering frankness, that it was someone else.
The in-show tribute video at the end actually did bring tears to General Hammond's actor, a former Vietnam-era army sergeant. And for the regular viewers... fan-favorite regular Dr. Fraiser is killed instantly while treating a wounded airman during an offworld firefight. Her memorial service consisted of a list of names of people she saved during her time at Stargate Command.
This episode was a real soul-crusher for the fans.
The season 1 episode, "Torment of Tantalus". Earnest, having been alone on a world for 50 years with no human contact, hugs Daniel and says, tearfully, "About time."
More of Daniel: when he is temporarily schizophrenic in the episode "Legacy", the team comes and visits him while he's institutionalized. He looks up at them and tearfully whispers, "They took my glasses..."
Even though they got out, Daniel blowing the sub that Jack and Teal'c were on in "Small Victories". Then, inversely, Jack blowing up a ship that he thought Daniel was on in "Scorched Earth".
The scene with Daniels parents in "The Gamekeeper", absolutely every moment of "Forever in a Day", him being left behind at the end of season one (although Jack's face just made it worse; Daniel seemed pretty calm about the whole thing), etc.
Teal'c accepting that he should be executed in Cor-Ai.
Sam gets one in "Singularity", when she is ordered to leave Cassandra in the reactor silo, since the Goa'uld bomb in her chest is about to go off.
Cassandra: You promised you wouldn't leave me. You promised. Sam: I know... but I have to. Do you understand? (choking back tears) Just remember, you're very brave...
The look on both their faces as Sam closes the blast door, Sam crying her heart out on her way back up the elevator, and then turning the elevator around and going back to wait out the timer with Cassandra is one uninterrupted seven-minute stretch of tear-jerking.
The end of "Tin Man", when androids who, essentially, have the minds and identities of SG-1, agree to live in exile.
Jack and Daniel's conversation in "Abyss" while Jack was being tortured by Ba'al and Daniel was dead. It alternates between hilarious and heartbreaking.
The beginning of season 7, when Daniel returns from the dead and doesn't remember his team. There's this moment where he just sort of shrugs, says they don't look familiar, and walks past them. The look on Jack's face is plainly heartbreaking.
Basically half of the entire episode "Cold Lazarus" would qualify, where we see how much the lives of Jack and his ex-wife were impacted by their son's death.
There's a really powerful moment where the crystal-energy doppleganger visit's Charlie's room and relives Jack's memory of his son's death, and comes out the other end a sobbing, shaking wreck. Especially potent when later on, the crystal Jack says that physical death doesn't have the same meaning to its kind, so it's experiencing a grief that it never even comprehended before.
"Lost and Found", when Re'tu Charlie asks if Jack can be his dad... sniff.
Then poor Re'tu Charlie has a front row seat to his "mothers" murder.
The end of "Window of Opportunity". When they are trying to get the guy to shut off the machine, the man behind it all says that he wants it so he can see his wife again, believing that Jack could never know that kind of pain. Cue Jack's patience finally evaporating;
O'Neill: I lost my son! Believe me, I know! And as much as I miss him... I could never live through that again.
When Jack is under the influence of Apophis' hallucinogen and he sees Charlie.
The end of "Divide and Conquer", Poor Martuf...
How about the look on Fifth Replicator's face end of "Unnatural Selection" when he realizes Carter and the others are leaving without him.
"Lost City" closing with Jack frozen in the Ancient outpost.
Skaara's brief return to consciousness after a year as host to a Goa'uld in the first season finale is pretty painful. "O'Neill, are you still my friend?" Sob.
The destruction of the Prometheus in "Ethon", complete with Pendergast's Heroic Sacrifice.
The worst part is that after years of service, and having saved at least two galaxies from annihilation. Prometheus is lost and the civilization it was destroyed trying to save wiped themselves out hours later. Talk about a Senseless Sacrifice...
"Serpent's Song" has a gutwrenchingly beautiful moment where Daniel is comforting the poor, thousands-of-years-old scribe who was Apophis' host by telling him that he will give him the final rites and that he will see his wife again after he dies.
Even worse that he didn't die and had to remain a prisoner in his own body for two more years. Imagine it thinking your millenia of torment is about to end as you slowly lose consciousness, then waking up again to find that your are still a prisoner in your own body.
In "A Matter of Time", when Jack realises that the thing Henry Boyd's team are so afraid of on P3W-451 is a Black Hole. Made even worse because Jack was the one who recommended him to lead SG-10 and it was his first mission through the Gate. The look of guilt on Jack's face says it all.
Then his furious What the Hell, Hero? to Carter, when she becomes almost giddy at the prospect of being the first to observe the effects of spaghettification, pointing out all they will see is good men dying horribly. Cut to the MALP, which is now showing Henry Boyd frozen in time, screaming in terror.
Jack refusing to forgive Cromwell for leaving him for dead during the Gulf War, which meant he spent some time in an Iraqi prison. Then shortly afterwards, Cromwell's safety-line snaps and he gets pulled through the gate.
In "Fragile Balance", when you realize that Jack's clone is roughly the same age that Charlie would have been...
"Avatar" can be brutal to watch, as Teal'c suffers slow mental exhaustion after becoming trapped in a computer simulation that will not let him win. He "dies" over and over again, no matter what he tries, and finally suffers a Heroic BSOD when he realizes the game won't even let him quit. It gets worse when it's revealed why he can't beat the simulation: the program's not malfunctioning. It's learning from Teal'c's personality and experiences, and tailoring the simulation to fit them. The reason Teal'c can't beat the Goa'uld in the game is because subconsciously he still believes that he'll never be able to beat the Goa'uld in real life.
That, and watching the virtual SGC personnel die over and over again. It's true they aren't the real people, but still...
When Sam's dad Jacob died for real.
Landry's death in the time dilation field and Mitchell standing on the bridge.
In the same episode, Vala crying during that conversation with Daniel, even though it ended well for both of them.
Thor at the beginning. His entire race is about to die, and his one regret is his inability to help the humans more than he already has. Selfless to the end.
When Sam says goodbye to Thor for the last time, just before the Asgard hasten their impending extinction by committing mass suicide.