"Ancient sadness. Star-city circling the burning nest. The silver horde has re-arisen. Kaelis-Ra awakes afresh, the ancient war resumed, spiderscuttle sorrow, all interlinked. Death in wraithsword, spirits infinity-bound. All is cyclical, all goes round yet remains the same. Always the dirges of echoes, always the bonework reflections.There is nothing new; just regret and impotence. We are lost in the dark, and there is no morning."
Comissar Yarrick, after a great, bloody battle of Armageddon. Some would say he stood stoically over the bodies of his comrades. Those who know him would say he wept.
The Imperial Guard: "We do what we do best, We die standing." Remember that they are normal humans in this darkest of settings.
Captain Blane from Gaunt's Ghosts really deserves his own entry. Tasked with the rearguard to protect against a rumoured attack by supposedly friendly units with an axe to grind against the regiment, Blane finds himself and fifty men under his command locked in battle with a far superior (in numbers and equipment) force from a rival (but supposedly allied!) unit. Fighting to the last man, Blane manages to hold them off long enough for the rest of the unit to get to safety. The most tearjerking part is that the rest of the unit had already gone on ahead, so would have no idea how hard and how long Blane's men fought, and how many traitorous scum they managed to kill before being overwhelmed.
The fate of Isha, the Eldar goddess of healing. When Slaanesh was created, s/he took Isha captive. Eventually, her cries for help were heard by none other than Nurgle and, being an affable sort of chap, he took pity on her and waged a massive war in the Warp against Slaanesh. Eventually he succeeded in "rescuing" Isha, and now he keeps her as a beloved companion. However, this is still the Chaos God of disease and corruption we're talking about, so he likes to show her his affection by brewing up new diseases and testing them out on her, seeing how long it takes her super-powered godly immune system to overcome them. It is a typical Tear Jerker for 40K: horrifying, sickening, and thoroughly heart-breaking.
If anything the Eldar suffer can make you feel sorry for them, it's things like the pagequote. The reason Eldar are so much The Stoic is because they feel emotion in far greater extremes than humans and therefore need to maintain constant self-control; underneath that mask of coldness lie feelings all too human. One of their non-combat based Path systems is the Path of Grief, whose followers' task is to express the misery and despair of the Craftworld that no-one else can.
Warhammer Fantasy: A short passage in the High Elves fluff about a regiment of spearmen that held the pass at Tor Yvresse against an entire goblin horde, with about a dozen surviving when Eltharion finally broke through to reinforce them.
New World of Darkness: Promethean: The Created is powered by this. You are born an unnatural horror, woven from the corpses of dead men. Nature rejects you, cursing wherever you are with desolation and misery. Humanity rejects you, giving you nothing but hatred and scorn. Even the divine fire within you flares up, drawing the attention of aberrant, failed monsters that were created to be your kind, but now seek to feed on you. In your journey doubt will take you away from your path. Embracing inborn monstrosity will become more attractive than anything else. Most of the time, you will not know what it means to have a friend, much less a lover. Every moment of your existence is miserable... But why do you persist? To follow your quest, to accomplish your Great Work — to become merely human. It's a definite tear jerker, though it can be either a heartbreaking one or a heartwarming one.
The Exodus of Alexandr Kerensky and the Star League Defense Force. It's quite depressing to read about these men and women, forever leaving behind the remnants of the Star League they dedicated their lives to, knowing that their hard-fought battle to liberate it from an Evil Chancellor has been for naught thanks to the convoluted politics of the Successor States trying to fill the power void left by the war.
The House Steiner sourcebook mentions the wife of one of Kerensky's aides who loved her homeworld in the Lyran Commonwealth and her husband equally greatly. When Kerensky's Exodus came, she was torn between her home and her husband. She ultimately chose to join the Exodus, but her decision came too late and she arrived at the spaceport just in time to see the ship carrying her husband leave forever. Take a guess as to what she eventually does.
The short story "Requiem" from the FedCom Civil War sourcebook. It revolves around a Lyran lieutenant squaring off against a Davion MechWarrior. After the Davion 'Mech brings down several of the Lyran lieutenant's lancemates, he finally manages to kill his opponent. And then finds out that the enemy MechWarrior he just killed was his own son.