I've been very fortunate to have had quite a lot of impactful sessions in my 40 years of gaming.
There is pretty much nothing cooler than those moments when a game's story, or a PC's actions, elicit real emotion from those gathered at the table.
As a player, most of my experiences with truly impactful sessions all come from the three and a half to four years I spent as part of my friend Will's Champions game, The Age of Chaos.
Particularly notable moments there included the murder of NPC Tachyon's sister, with Tachyon, and Nightforce (a PC) failing to prevent it.
Tachyon's powers had overloaded several years earlier, and he could no longer use them safely, so he had retired from the Superhero scene. When a villain named Mr. Grim revealed he had kidnapped Tachyon's sister (a popular and well respected reporter), and threatened to kill her, Tachyon activated his powers without thinking.
While this shot him from New York to a small island off the Greek coast in the blink of an eye, the initial activation caused an explosion in the small alleyway in NYC where he had been standing that set the very air aflame for nearly two city blocks in all directions. Two fellow heroes (one a PC, and one an NPC) were killed, and another badly wounded.
Tachyon still arrived too late. Just before his rage overtook him, and he purposely took Mr. Grim's life, the PC hero Nightforce (who had been investigating, and tracking Grim for days), appeared out of nowhere (having already infiltrated Grim's hideout), and killed Grim himself with a sharp 'Nightarang' to the head.
The UN officials that oversaw our super team, Project: UNITY (a sub-division of the international SHIELD-like organization known as UNTIL) deemed Tachyon's actions an accident, and Nightforce's actions necessary (he actually threw his weapon a split second before Grim fired the gun that killed Tachyon's sister - but it simply didn't travel as fast as a bullet going only a few inches away).
Nightforce reveals that in his secret identity, he had been dating Tachyon's sister for the better part of a year. Tachyon didn't except the UN courts' decision. He deemed himself guilty, a danger to his fellow heroes, and the citizens of the world. Racked with guilt, he had himself committed to the maximum security super-prison known as Stronghold.
Tachyon and Nightforce
That was just one of a dozen such stories like it from that campaign.
As far as games I've run, again, there are a lot of sessions I can call upon, though one particular one springs to mind. Is was, believe it, or not, in D&D...
I hope I can do it justice in the retelling.
I'll take you back in time to 1998-99. We were a little further than mid-way through the first campaign of Dungeons and Dragons I'd run with my ex-wife/then girlfriend.
As noted in the past, my D&D setting is very much influenced by Superhero comic books, Japanese Anime, and folklore. It has been referred to by friends as D&D-But-Not, or D&D-for-those-who-don't-like-D&D.
In this particular session, my ex-wife's character, the High Elven Warrior/Wizard Ceren-Dee WindDrake, was leading part of her team on a mission to chase down the group's arch-nemesis, the Ash Elf known as ShadowHaunt.
While Ceren-Dee, Vek-Tan the Mighty (a Half-Ogre), and a Wilder (Halfling) named Thistlecap, sailed with Captain Praxis, and the crew of the Siren's Wake, in pursuit of ShadowHaunt's vessel and the previously-thought-mythic 'Pearl of The Ages' he had obtained, Ceren-Dee's commanding officer Sheylan Everleaf, and the good hearted, scoundrel Dalor EverBurnGreen fought off ShadowHaunt's minions (undead Sailors, and Pirates) to enable the Siren's Wake's departure.
Sheylan and Ceren-Dee had recently revealed deep, romantic feelings for each other, and it was with heavy heart that she left him behind to fight the hordes of Dave Jones' locker. It was his order however, as he knew Vek-Tan to be the best sailor among them, Thistlecap to be the most crafty, and cunning of their lot, and Ceren-Dee's magic over wind, and weather to be the best chance they had of catching their enemy's craft.
Victory seemed likely until a rogue wave, an unexpected, rolling wall from the Northeast, crashed into both ships. The 'Pearl of The Ages' fell overboard into the sea as a second, even larger wave crested the horizon. When it struck, Ceren-Dee, Vek-Tan, and Thistlecap were hurled into the briny deep.
Using their respective skills, and a good dose of teamwork, the three heroes of the Winghorn Guard managed to surface, but their ship was nowhere to be found. They drifted for hours, perhaps half a day, watching an oddly dark, clouded sky roil above them. A storm? No. Something more ominous...
Eventually the trio was picked up by a passing ship, which bore the markings of the Winghorn Guard, though it was in dreadful shape and crewed by weary, war worn sailing men, and women. The crew was shocked to see them, as if they were ghosts. Eventually the heroes were introduced to the ship's captain, and his wife...far older versions of two of Ceren-Dee's dearest friends, and allies, Gestral Ronnamoor, and Promelia of Thebius.
The Pearl and it's magic had sent the trio of heroes through time, into a very frightening future. This world was under the protection of a Winghorn Guard that tolerated no infraction of law and order, no matter how small. Nations had been toppled, orders of knights disarmed in favor of the constantly watchful eyes of the Guard. Gestral and Promelia had left the order years ago, unable to be a party to it's growing totalitarian attitude.
The heroes (the players) had automatically assumed ShadowHaunt had somehow taken over the Winghorn Guard and used it to rule the world. Not so. ShadowHaunt was dead, killed by the Guard member who changed the protectors of peace into an army of oppression.
Apparently, when Ceren-Dee, Vek-Tan, and Thistlecap disappeared from the timeline, it was assumed that ShadowHaunt had killed them. Sheylan, heart-broken to depths even he did not realize, spent the days, weeks, months, and years that followed hunting ShadowHaunt down. If ShadowHaunt made allies, Sheylan slew them. If he had a plan, Sheylan thwarted it. Everleaf grew in power, rank, and prestige until he essentially lead the Winghorn Guard. If he thought you might be in league with ShadowHaunt, or even in league with an ally of ShadowHaunt's, he would find you, and you would burn.
The scenario and the subsequent travels through this dark future brought honest-to-goodness tears to my ex-wife's face. All the participants played their parts with Oscar-worthy performances. It was a pretty emotionally draining session, but it ended with tears of joy.
Ceren-Dee and her teammates tracked down future-Sheylan, and showed him that not only was Ceren-Dee not dead, but that there was a way to prevent all this sorrow and anger from spilling out and drowning Aerth (the campaign world). However, in a fantastic speech by my ex-wife, she explained that no amount of love makes up for the kind of anger, and hatred that could doom a world to what this one had become. She realized Sheylan held deep within him a great darkness, even in her own time. He loved her yes, but he needed her as well, to teach him how to lose, how to fail, without carrying the burden of the world on his shoulders and a fire for vengeance in his heart.
Eventually, the team worked to locate the Pearl of The Ages, and counteract its effect using a clever interpretation of a spell in their repertoire (I forget which one). By combining the power of the spell, the Pearl, and other bits, the trio of time travelers were able to return home.
When Ceren-Dee and Sheylan were reunited, there were more tears, a lot of laughter, and discussions about the present, and the future.
Until next question...