Chrono Trigger had some awesome ones but the three that stand out is finding out what happened to Ozzie, Flea, and Slash; getting the Rainbow Shell which was a great cinematic sequence for a 2D game; and easily restoring the Forest and saving Lara from being crippled. Chrono Trigger knew how to take little things and make the game feel grand in doing them.

FFVI a lot of the sidequests are pretty awesome in this game. I love discovering Shadow's past through his dreams, Cyan's Dream World, fighting the Hidon was an interesting dungeon concept, and the Ancient Castle and learning about Odin and getting a little taste of the War of the Magi.

Suikoden II has recruiting McDohl which is both just an awesome quest and an emotional roller coaster ride for players of the first game, especially if you bring Flik, Viktor, and Sakura with you. It's also one of the coolest payoffs in the series as well. Second is Hai Yo's Iron Chef themed sidestory which was totally awesome and had me hunting down all those recipes. Luc is an asshole cause he hates everything...

FFXII - The Man of Mystery Mark hunt, both parts, it was glorious...

Chocobo Racing is great in FFVII

Triple Triad is still the best mini-game in the FF series.

Xenoblade Chronicles has several good ones but my favorites were helping the inexperienced officer who was trying to get promoted, the drug ring was amusing as all hell, the warring chefs in Colony 6 were amusing, and the Telethia missions in Alcamoth after the games main villain reveals themselves, you never saw the build up for these missions and they are some of the more poignant missions despite being simple Mark Hunts due to the games larger narrative.

Fairy Colony in Breath of Fire series.

Mini-Medals in DQ games, I know its simple and silly but I always look forward to them.

The Brotherhood missions in Assassin's Creed Revelations, really made recruiting the characters get more personality and make the Brotherhood feel human.

Moira's Wastland Survial Guide in Fallout 3

Recruiting all the extra characters in Final Fantasy Tactics

Acquiring adult Emerelda in Xenogears since it fills in more backstory for Kim, and the Zeboim Era.

Building Erde Kaiser in the Xenosaga games, also fighting Welltall~Id in Episode 3.

The Elizabeth Dates in Persona 3 FES

Getting the Mastermune in Chrono Cross

The botched demon summon fight with Mara and getting the final Magatama in Shin Megami Tensei III

Finding all the Easter Eggs in Metal Gear Solid franchise.

Fun City in Star Ocean 2

I'm sure I'll think of more later...