I get ya, I felt the same way for a while, then I realized it's more that there are still so many great RPGs I hadn't yet played, many of which I now count among my all-time favorites ^^
To answer my own question with the first ones that immediately come to mind...
Persona 3: (SPOILER)The scripted battle against Nyx. I had the fact that the MC dies at the end spoiled for me, but that didn't make it any less impactful, especially seeing the HP cost of The Great Seal god I love this game.
Final Fantasy X: (SPOILER)Finally reaching the Zanarkand Ruins, IMO the most powerful part of the game. At this point everything Yuna believes in has been shattered, she may as well be a walking corpse heading to her grave doing one last actually meaningful thing in her life, and Tidus can only look and watch her do it. The mood and tone is set so perfectly.
.hack//G.U.: (SPOILER)Many moments to choose from, and almost all of them in Volume 2, go figure. But I gotta go with the confrontation with Atoli. Atoli's screams of her own perceived worthlessness are so damn haunting, and as cute and lovable as Alkaid is, to me Atoli is ultimately the real star of the best part of G.U. and her relationship with Haseo is so damn well-written.
Trails of Cold Steel II: Gotta put two moments here, sorry >.<
1. (SPOILER)Rescuing Princess Alfin. The entire Intermission is my favorite part of the game but Rean running off with the princess is exactly how you take a cliché and make something incredible out of it. The cast of antagonists is the real star here and god are they all wonderful.
2. (SPOILER)When Osborne walks onto the scene after Crow dies. The entire finale we've been building up towards is brought to the conclusion we've been waiting for, only for Crow to end up giving his life. Leaving Rean incapable of fulfilling his real personal goal in this whole mess. And then this asshole shows up and uses everything you've been fighting for to his political advantage before you even have a clue what's happening.
I think the best way I can put why I love this moment so much is because it embodies exactly what I love so much about this series; it both manages to play the 'journey of the hero' and have you feel super awesome as you accomplish more and more in the story, and still have you feel insanely empty and unfulfilled by the end of it because, in the end, our feats grow too big for our own good and end up being used for a *much larger story* unfolding in the back of everything.