Hmm, good question. I'd have to think on it awhile, but two come to mind right away. The first is from shortly after I started playing Sims 1. I started with the Newbie household, and I played them all day. I had no idea what I was doing, but whatever it was, I loved it. When they made food in Sims 1, there weren't individual dishes that you could tell them to make, it was just prepare food. If a counter space and stove were available, they'd make a fulfilling meal that raised their hunger bar a lot, if there was a counter space but no stove, they'd make a salad that was moderately fulfilling, and if there was no counter space, they'd just pull a tray of cans out that were ready to be eaten right away, but weren't very fulfilling at all. At first, I liked it better when they just pulled the plate of cans out of the refrigerator because it was so much faster than actually preparing a meal. But after awhile, I realized that the food quality was much, much better when they prepared food, so although it saved time in the preparation, the "instant meals" weren't worth it, so it was better to be sure that the counters weren't cluttered and they could prepare a proper meal. It was then that I began to realize that there was more depth to the game than visible right from the start.
Remembering this really makes me wish that I could go back in time and play the game for the first time again.
The other moment is much more recent. In Sims 3, I've been working on a Legacy challenge. Well, if I like the spares, I usually let them stay in the house instead of kicking them out, they just don't get to marry or have children. One of them, named Clove, was a really sweet Sim I became pretty fond of. He was a genius, angler, easily impressed, good, and lucky, and he was just a happy Sim who was easy to please, did well in his career as a doctor, and got along with everyone. He made friends with a (really, really ugly) townie named Pauline in school, and I let their relationship progress to the point that they became engaged, but it had to stop there.
So, when I noticed his life bar getting full, I knew that it wouldn't be too much longer until he died, and I invited Pauline over, so they could spend his last day together.
Unfortunately, I misjudged exactly how full his bar was, and shortly after Pauline arrived, the reaper did, too.
So I lost one of my favorite Sims, and Pauline lost her fiance. However, she didn't leave the house that night. I was expecting her to go ahead and autonomously go home when it got late, but instead, she stuck around the house for two days. One of my Sims was an explorer so I had a couple of sarcophaguses on the lawn, and she slept in those. During the day, she'd go in the hot tub, eat meals that were prepared by family members, chat with them, and use the various objects that I had on the lot.
Finally, early on the morning of the third day, she got in her car and left. She drove one of those cheap cars that you see all over (Sloppy Jalopy?) and, as she jumped in the car and drove away, she actually turned to look back at the house.
It was really an amazing moment. She stayed the whole time that she and the family were in mourning, and then, after her period of mourning was over and she had to leave, she took one last look at the huge, expensive mansion, knowing that she'd never see it again, as she was an elder too, and she had no reason to come back since her fiance was dead and she wasn't friends with any of the other Sims.