This is based on opinion. Please don't list it on a work's trope example list.
Tear Jerker / Silent Hill 2
"I have been emotionally ravaged by a total of two games: the first is Silent Hill 2. Iíve been to enough conventions and talked to enough people about it that I know I am not alone in this. There are many ways to interface psychologically with the game, but if you are a sentimental husband with a young, beautiful wife, the game is precisely calibrated to annihilate you."
Room 312. The conversation you hear between Mary and James in the hallway, where you begin to understand how sympathetic James really is, how hard it must've been for Mary, and for James to take her abuse and mixed messages. The In Water/Leave endings are heartbreaking when finally James confesses, guiltily and tearfully: "The truth is... I hated you. I wanted you out of the way. I wanted my life back!" The In Water ending is, of course, much worse, because despite Mary's urge for James to move on... he doesn't.
The In Water ending even changes Mary's final message. In the other endings, there are a few lines after Mary tells James to do what's best for him. In the In Water ending, it ends after "Do what's best for you, James." Think about that.
Mary's response in the Leave ending to James saying "I hated you," is devastating in a completely different way. "If that were true... then why do you look so sad?" (shudders)
On that note, the full text of Mary's letter, which you finally hear in the end. VO actress Monica Horgan has said that some of the ADR crew began to choke up during her final recording of the monologue; can you blame them?
That line could be interpreted as an admission that her mother's cruel and inhumane condemnation of her was the final straw.
The hospital basement segment wherein James is helpless to save Maria when the elevator doors trap her outside, but remain open just enough for him to see Pyramid Head brutally murder her. James slumps to the ground with a look of utter defeat while the most somber music in the game fades in.
Any and all scenes with Angela.
Particularly tough for some is when Angela calls James out for white-knighting. If the player really has been seeing her as someone to help, the rebuff rings humiliatingly true. Even more so for James—even if Angela could be helped, he's not the person who can help her, due to his own issues, and he knows it.
Angela: No, don't pity me! I'm not worth it... Or maybe, you think you can save me. Will you love me? Take care of me? Heal all my pain?
(James hangs his head in silence)
Angela: *bitterly* That's what I thought.
"You killer! Why'd you do it?! I hate you! I want her back! Give her back to me! I knew it, you didn't care about her! I hate you, James! I hate you, I hate you, I hate you!"
"James... You made me happy."
"Look! I'm disgusting! I don't deserve flowers. [...] Are you still here? I told you to go! Are you deaf!? Don't come back! ... James... Wait. Please don't go... Stay with me. Don't leave me alone, I didn't mean what I said. Please, James. ... Tell me I'll be okay. Tell me I'm not going to die... Help me..."
Maria's entire story in Born From A Wish. She starts out as just another person lost in a town filled with fog and monsters, contemplates suicide, and somehow finds the strength to go on. Displaying her kind personality in helping someone who's only dismissive of her, she discovers what she really is. It's hard to say if she was looking for revenge on James since she had Mary's memories of being killed by him or if she was still somehow hoping for a happy ending.
Going back to Room 312, the song "True" is so amazing. It mixes all of the elements that the game is: mystery, sadness, pain, etc. It is a perfect song for the perfect scene that even listening to it without the game's context makes you feel somewhat empty.
Most of the endings fall into this category.
In Water: James succumbs to his despair, and, in spite of Mary's tearful plea for him to move on, he cannot, and commits suicide.
Maria: Despite his experiences in Silent Hill, James isn't ready to accept that Mary is gone, and latches onto Maria. What drives it into tear-jerker territory is that the cough at the end shows that history is doomed to repeat, and just like Mary did, she will get sick, and die.
Rebirth: James cannot accept that Mary is truly gone, and is willing to invoke the dark forces of Silent Hill to try and resurrect her.
There's something profoundly sad about the final boss being reduced to simply murmuring "James..." before being finished off, regardless of who it is. Maria is a Tragic Monster who can't do anything but revolve around him even in death, while Mary, despite starting out bitter and vengeful, seems despairing rather than angry.