For me, it was facing off and repeatedly dying to the final boss in Paper Mario: The Thousand Year Door. I loved how appropriately difficult the battle was and how epic the music was during it. The cutscene that plays before the final phase:
I loved seeing this the first time around, because I'm a sucker for 'everyone prays for the hero' scenes. The only problem was, if you died during the battle, you had to see this cutscene all over again. Without being able to skip it.
Did I mention that the boss was difficult?
What was your worst experience from your favourite game?