There were a few for me, but only as a player. (I haven't GM'ed a whole lot, so I haven't been able to instill that kind of emotion into players as of yet.)
The first was a Vampire: TM game I played in. The GM had a pretty well-thought-out story into which we PC's got involved. After investing an entire year into this adventure. (It was a weekly game and we managed to play pretty close to every week.) Despite having the story be essentially be about the struggle between 3 ancient vampires (we chose the side of one of them) the GM was able to make us feel like we were an important part of the overall story. The last game was pretty emotional to say the least. Of course, partially it was about the game ending, but it didn't help that we had to watch Seattle (which, in game, was our home for that entire year) get nuked, and not be able to do anything about it. (Long story)
The second was a D&D game. A pretty major side plot to this game was that this guy accidentally killed the favored priest of the FR goddess of disease (forgot her name), when the ship he owned (filled with gun powder) blew up. The disease goddess, angry about this, decided it was time for a major plague. So, she diseased him in such a way that he was highly contagious, but could not die.
This plague was basically a rotting disease, and the guy obviously was infected (he looked rotted). So, he would be burned alive with everyone else who had the disease. Unfortunately, he felt the pain, but wouldn't die. Another effect of this disease is that if you came within a certain distance of him, you had the disease, but if you stayed within that distance, it wouldn't advance. (It was harder to contract the disease from someone else.)
So, we kept him around for some time, and went off to investigate ways to stop the plague. We discovered that near Silverymoon was a Moon Pool which was, of course, dedicated to the Moon Goddess Selune. We went there, and discovered that the pool was in the hands of a group of undead which we had to destroy. Then we went into the pool and got the disease removed. However, we found out that the guy (Fallon is his name, I believe) couldn't have it removed. Selune could not stop that aspect of the curse...she could, however, make it so he could die, and therefore stop the disease. We sat there depressed while Fallon went off with one of his black powder pistols and...well...you can figure out the rest. Very sad.