The look on Daniels face in "Children of the Gods", when he realizes that Sha're 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.
Jack repeatedly calling Kawalsky by his first name throughout the episode, leading the audience to realise just who Jack named his dead 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.
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 doppelganger 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.
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."
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.
Earlier in that same episode Cassandra sees the team tagging the dead and takes one, and is later shown putting the tag on herself essentially marking herself as dead.
The end of "Tin Man", when androids who, essentially, have the minds and identities of SG-1, agree to live in exile.
"Forever in a Day", especially the part when General!O'Neill is killed. He just kinda stands there in a daze as it happens.
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.
Daniel 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.
The scene with Daniels parents in "The Gamekeeper", absolutely every moment.
In "A Matter of Time", when Jack realizes 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.
"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.
"Show and Tell", 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.
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..."
Sha're's death at the hands of Teal'c is uber heart-wrenching to watch.
When Jack is under the influence of Apophis' hallucinogen and he sees Charlie.
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 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... I could never live through that again.
This episode was a real soul-crusher for the fans.
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.
How about the look on Fifth Replicator's face end of "Unnatural Selection" when he realizes Carter and the others are leaving without him.
In "Fragile Balance", when you realize that Jack's clone is roughly the same age that Charlie would have been...
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. Samantha Carter delivers her euology.
We often talk about those that give their lives in the service of their country. And while Janet Frasier did just that, that's not what her life was about. The following are the names of the men and women who did not die in service, but who are in fact alive today because of Janet. Major Samantha Carter, Dr. Daniel Jackson, Colonel Jack O'Neill, Teal'c, Sergeant Connie Smith,Major Ian Hules, Senior Airman Simon Wells. . .
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.
"Lost City" closing with Jack frozen in the Ancient outpost.
"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.
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...