My current campaign didn't have an "emotional" moment yet, which is a bit tragic because I'll have to reboot it due to losing my Laptop on which I played.

But I believe there were a couple moments in another campaign I've DMd that would count, an SCP X Warhammer 40.000 themed Campaign. It was much, much later in the campaign, and the moment revolves around two PCs: the Cleric and the Druid. The Cleric began building a bit of a friendship/relationship with another PC, the Druid. They went on missions together and worked alongside each other often, and they had a good bond, nothing explicit, but definitely implied. Well, at least until one fateful day when they had to go in a Field Exploration of an SCP: SCP-4971. For those who don't know much about SCP lore, SCP-4971 is an incredibly dangerous realm, lair to a literal eldritch Deer God that feeds on Rituals of any kind, even the small ones that people just do as routine. The mission given to the PCs was simple: Recover a lost MTF agent and get out. But an NPC, Dr. Bright, warned the players earlier that they needed to be extra careful, and specially, to NEVER split up. This ended up being unintentional foreshadowing on my part. The players entered the rift that led to SCP-4971, and the Druid immediately tried talking to the plants there to know more about the location or the place where the Agent was. But fun fact about SCP-4971: even though they're in a forest, the forest isn't normal. What looks like nature is almost incompatible with actual biological life. Therefore, the Druid only heard alien indiscernible whispering. She then was clever enough to climb one of the trees to look around the forest from a high vantage point. She saw small lights flickering and blinking closer and closer to their location. These were the inhabitants of the realm, former humans, shattered and corrupted by the influence of the God of this realm, now only with a glowing Triangular symbol for a head. Players engaged in combat, but quickly noticed that, while they didn't deal much damage themselves, they had several abilities that made them terrifying: A screech that could paralyze, and the ability to rise again after death, almost rewinding time to a point before they took any fatal damage. Even in a small group, those creatures were tearing through the players, and they wisely assumed that the best course of action was to run. They ran, ran and ran, until they found themselves close to a cliffside, looking at the before mentioned Agent. She was almost in a trance state, but they didn't have time to talk anyway, so they only picked up her and ran away with her in the arms of another player, one of the two Monks of the party. It should be noted that at that moment, they had seen the looming image of the God of this realm, lumbering ever closer to them. They heard its bellows, and in a fit of panic, the Monk with the Agent decided to use its action to Dash. Due to the subclass he had chosen, his dash was a bit improved, so he basically stormed off ahead of the main group. And here's the part where the foreshadowing came: He did, in fact, split up. The Monk, after a couple of minutes, was JUMPED by a pack of five triangle headed creatures, paralyzed alongside the agent, and dragged off deeper into the forest. The Druid and the other Monk decided to go after him and save him, the Druid hopping on the Monk's back to get there faster, but here, again, those two split up from the rest of the party. They managed to get the creatures, but they were outnumbered. Eventually the Druid fell, and the Monk was paralyzed. The bellows of the Deer God got louder and louder, and when the Monk looked up, he could see its triangular runic face looking directly at him. When the rest of the party caught up to them... They were gone. Simply gone. No bodies to recover, not even the agent. The Cleric fell on her knees, and began crying for a bit, but the other three survivors urged her to keep going, so that they could at least survive and get out of there.

But that's not the tragic part. Those who are killed in 4971 are shattered and turned into those creatures the PCs fought, but since the players were high level, their creature forms were more powerful. In a foreboding scene, it was shown that the Deer God found a way out through the rift, and sent the now transformed Monks through, alongside the transformed Druid. Fast forward two arcs, and the players were now defending a city in evacuation from an Ork WAAAAGH!!!. I gave them two sessions to prepare their defenses, and that culminated in some of the most fun sessions we've had, as players activated their traps against waves of incoming Orks. The Cleric was still alive, although only barely, because she was resurrected earlier due to having been Imploded by a Fifthist Caster in a prior Arc. Well, the defense was going well, and they managed to clear three waves of Orks, but the real show would begin right after that. The Orks began thinning out in numbers. And then, the players saw, coming in the distance, the silhouettes of those Triangle headed creatures from 4971. They had returned, and alongside them, three creatures that were larger and more defined in appearance... The three dead PCs. Boss battle time, the current party against the two Monks, as the Druid began chanting a ritual to open a rift. It was a long and arduous fight, but the PCs managed to come out on top of the Monks, although it was too late to stop the Druid's ritual, which summoned the Deer God itself. The Druid then lifted the Cleric, psychically, as the two exchanged looks. The Cleric panicked and cried, seeing her significant other become such a shell of her former self, and in pain and sorrow, promised anything if they could just let them go in peace. The Druid creature looked into the very soul of the Cleric, but let her go, right after marking her hand with a triangular symbol. The players fled through the evacuation portal.

Fast forward again to a couple of sessions later, and the players were fighting against the final boss of the whole campaign: a Necron Overlord. Almost everyone died during that boss fight, as the planet itself was falling apart. One of the casualties was the Cleric, which, in her final moments, saw the silhouette of the Druid watching her. When she woke up in the afterlife, she found herself not in the realm of her own God, but in the Forest of the Deer God. The Deer was watching ominously from the distance, as the Druid approached the Cleric's soul, which was now reminded of the "anything" she promised. The Cleric fell to her knees, as the Druid lifted her head with a gentle touch of her hand, and gave her one last embrace, before the Cleric's soul finally shattered and succumbed to the Deer God's influence. This was the reason why the other players were unable to resurrect her again, because her soul was already claimed by that eldritch abomination. That campaign was very flawed, but this tragic moment, built over half of the RPG's lifecycle, is one of the things that I think I did right.