This is based on opinion. Please don't list it on a work's trope example list.
Tear Jerker / Night Lords
At the end of Blood Reaver, the death of Hound. Who knew that we could feel sorry for the mutant heretic?
Mita and Sahaal were hit by crises of faith, both led to believe that they were both lied to their entire lives and that those they admired and looked to for guidance (The God-Emperor and Konrad Curze, respectively) were Broken Pedestals. In Sahaal's mindscape, after an Eldar traps both of them in there, they confront each other and find that they are kindred spirits.
And then the ending (the real ending, before the sequel hook). Despite everything, "Ave Dominus Nox!".
In Void Stalker there are a few Tear Jerker scenes. The best is Talos Valcoran's death. He is the last survivor of 10th company, barring Variel and Lucoryphus, and knows that he is doomed to die from gene-seed rejection in a few years and admits that he never hated the Night Lords, he hated what they made him into. He calmly walks towards Jain Zar, the Void Stalker, and allows her to impale him before detonating every grenade he has to take her with him. He succeeded.
Then Cyrion's death. Cyrion is wounded by Jain Zar and dies in Talos's arms. He is blind from the burns and gives his last request to Talos to not have his gene-seed extracted, stating that he just wants to rest, which Talos promises. He then dies with the final line, "I'm dying," he said. "Everyone else is dead. The slaves escaped. So..." he breathed out slowly, "...how are you?" Definitely a tear worthy moment.
Uzas's death is worthy of tears, even more so because of what happens afterwards. Uzas finally realises that he is not responsible for many of the murders he has been accused of, Cyrion is. However, he manages to control his anger and tells Cyrion that they'll tell Talos about this so that he may decide Cyrion's punishment. It's obvious how hard he's trying to throw off Khorne's influence. Cyrion agrees... then tries to kill Uzas to keep him quiet. This finally breaks Uzas' self-control and he goes berserk. He kicks Cyrion off a cliff and jumps after him in order to finish him off. Before he can do so, however, Talos fatally wounds him, and tells him he is the worst excuse for a Night Lord in their history and that even Ruven was better than him. But before he dies Cyrion actually apologies to him posthumously, a traitor marine actually apologies to somebody and means it.
Xarl's death is much earlier on but is no less heartbreaking. He dies saving the rest of First Claw from a Genesis Chapter Company Champion. His last act is to tell Talos that he was wrong and that there is no shame in wanting more in life than just survival. He falls to the ground, dead, after finishing his sentence. Talos then carries out his cremation and harvesting himself, and cries after burning his friend's body.
"I am going to bury my brother."
The actual text detailing the end of Xarl's burial is just brutally painful, really. "‘No.’ Octavia cleared her throat, lowering her hands. ‘I heard about Xarl. I’m sorry. I think… by your standards, by the Legion’s ideals, I mean… he was a good man.’ Talos’s exhalation became a snort, which in turn became a chuckle. 'Yes’ he said. ‘Xarl was a good man.’ Octavia shook her head at the warrior’s sarcasm. ‘You know what I mean. He and Uzas saved me once, just as you did.’ The prophet’s chuckle became laughter. ‘Of course. A good man. A heretic. A traitor. A murderer. A fool. My brother, the good man.’ Both humans stood in silence as, for the first time in many years, Talos laughed until his black eyes watered"
Mercutian has a heartbreaking death as well. He is mortally wounded by Jain Zar during the fight in the catacombs and volunteers to stay behind and buy them a few seconds. He battles Jain Zar ferociously even though he is dying. She impales him and leaves him to die, giving him the chance to shiv her in the thigh. She finishes him off quickly, but the wound he gave her was the reason that Talos and the others had a fighting chance against her.
In the novella "Descent", the last use of the line "Nobody hunts like House Ty!".