"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.
The episode "Sunday", wich ends with Rodney talking to Carson, one last time.
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.
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".
Ronon's flashbacks in "Sateda". They get increasingly poignant as the episode drags on, starting with him begging his fiancee to leave with an evacuation group while he stays to defend their world (knowing that he will almost certainly be killed), continuing with him watching his comrades and friends die all around him, and ending with him standing in a hospital, paralyzed with guilt over his inability to save everybody on the ward...and then his fiancee is vaporized by an explosion right before his eyes.