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 Atlantis
"The Shrine". The whole episode will make you cry for so many different reasons. Especially when the team pressure Woolsey to let them try to save McKay, and Ronon says that he could never understand. Woolsey then tells them of father's Alzheimer's, where his father didn't recognise him for months, before a moment of clarity where he was his old self... and then he was gone again.
"Sunday", if you please.
McKay: You were the closest thing to a best friend that I ever had.
Not quite on the same level, but a particularly heart-wrenching scenario occurred when Dr. Weir told her fiancÚ Simon not to wait for her. He didn't.
Perna's death scene in Poisoning the Well.
As big of a jerk as McKay was about the whole "destroying 4/5 of a solar system thing", this troper still felt bad for him when Sheppard shut the transporter in his face, and really bad for him a few episodes later, when he asks Sheppard if he trusts him and Sheppard snaps back "No!"
Elizabeth's death in 'Ghost in the Machine'. Heartbreaking enough that she's gone for the last time but what clinches it is John standing alone in the gate room, clearly on the verge of tears.
The Season 1 finale when John leaves on a suicide mission. Elizabeth catches him and...lets him go.
Elizabeth: "You can't!"
John: "I have to - and you know it."
Elizabeth: "John...(shakes head)...Go."
Especially heartbreaking in that she could have ordered him to stay, but ordered his death for the good of Atlantis.
Also Fridge Brilliance: You initially think John is performing the Heroic Sacrifice, then you realize its actually Elizabeth as well. John is giving up his life, but she's giving up her closest friend and sole confidante of the last year. Its a culmination of what the pair would do to protect their people after a season of protecting the expedition, and its heartbreaking.
That one moment in "Ghost in the Machine". The city's systems shut down, one computer monitor suddenly brightens, and the team realizes that someone or something is trying to communicate with them through the computer itself. Rodney types in "Who are you?" and the answer comes slowly: "Elizabeth Weir" The music that accompanies that single moment is so absolutely perfect, it's as though the strings are saying "what have we done to you".