The ultimate fate of Martin Septim, who sacrifices himself to become an Avatar for Akatosh to permanently defeat Mehrunes Dagon and seal off Oblivion forever. This counts as a tearjerker, since aside from the potential of him being a great Emperor, Martin was himself a good man, who was the real hero of the story, not the player character.
When you're too late to save Lucien, and you never get to clear his name, either.
The Dark Brotherhood quest the Purification: you have to kill all of the members who have come to love you like family. This is made worse by the fact that you later find out there was no traitor — at least not in the Cheydinhal Sanctuary. You killed them all for nothing.
Made even worse with M'raaj-Dar who is a total jerkass to you right up until the beginning of this quest where he decides that the two of you got off to a bad start and wants to try and be friends.
The end of the Thieves' Guild quest line when you meet the Gray Fox in Anvil and he reveals himself to be Countess Umbranox's long lost husband, Corvus.
The quest A Brotherhood Betrayed. Three adventurers found an ancient amulet, but one wanted it for himself. So after many years, that traitorous adventurer murdered the other two in the guise of a vampire hunter, setting the other two up as vampires. The wife of one of the victims, shocked by what had happened, asks you to help. After the truth has been revealed and the seemingly mundane amulet retrieved, she reveals that the other two knew what the traitorous adventurer had been planning, and cursed the amulet so it becomes mundane. The secret word to lift the curse? Brotherhood.
The penultimate quest of the Fighter's Guild requires you to infiltrate the Blackwood Company and learn their secrets. As part of the quest, you're required to drink a bottle of Hist Sap, a hallucinogenic drug, and help the Blackwood Company clear out a town of a goblin infestation. When you return to the town after the drug wears off, you find that in your hallucination you mistook the innocent inhabitants and their sheep for goblins and slaughtered everyone in the town, including Biene Amelion, who you helped out in a previous quest. Her father, who you freed from jail by helping cover the cost of his bail, wonders what kind of monster would do such a thing...
The beginning, too. When you first meet Martin in the chapel of ruined Kvatch, before he even knows who you are or he is, he just sounds so weary and disillusioned. Possibly even Harsher in Hindsight, when you know how it all turns out.
"Yes. I'm a priest. Do you need a priest? I don't think I'll be much help to you. I'm having trouble understanding the gods right now. If all this is part of a divine plan, I'm not sure I want to have anything to do with it."
Another Dark Brotherhood quest, The Lonely Wanderer, makes me tear up every time I have to do it. To find Faelian, you have to ask around for info, and end up at the King and Queen Lodge in the Talos Plaza District of the Imperial City. You talk to the innkeeper there about him, and she mentions how he has a wealthy girlfriend that's apparently devoted to him. You talk to the girlfriend (possibly needing to chat her up into a friendlier level), and SHE tells you all about how even though Faelian's a penniless Skooma addict, she still loves him and hopes he manages to someday clean himself up before he gets himself killed.
There's a Fighter's Guild Quest where you read Viranus Donton's journal. He writes about how he thinks his mother doesn't think he's as good as his recently-killed brother. You find the journal when you find him dead in a mine. The final entry? "I hear trolls I'm sorry Mother".
In the Shivering Isles expansion, near the end, as Sheogorath turns into Jyggalag. Hearing the wacky, crazy, eternally-cheerful Sheogorath speaking in an orderly, logical manner and sounding so depressed.... And then he sadly tells you to get out while you can, or he'll kill you.... It's heart-breaking, really.
You can find a dead troll under a bridge. Funny? Yes, until you read the note on it's body, confirming that, yes, all those trolls you killed for their fat are sentient.
"Mee wurst troll evurr
nobuddy pay brijj tole
me nott sceary enuf
mee gett drunc an kil sellf
The 'Final Resting' quest, where you are asked to put someone out of their misery. The suicide note is just the icing on the cake.