Skyrim's sidequests are too good. Instead of using these rabbit trails to break up the pace, and add some colour to the overarching story, the side quests overshadow the main story until they blend together.
Final Fantasy XIII tried create a purer strain of "main story" by axing some of the mundane distractions like towns, monster hunting and general exploration. The result, coupled with the disastrous launch of Final Fantasy 14 online nearly scuttled the once highly respected name of developer Square Enix.
Thankfully, Final Fantasy XIII-2 has learned from its past mistakes to create a bridge between these two extremes; opening up the world for exploration, monster collecting and mini-games while providing a strong story to bring back the player's focus.
Because helping farmers deliver supplies, aiding deities in cleansing their temples and scouring endless caves for rare items is great, but without a main quest, without a terrifying battle to prepare for or an urgent crisis to pull you along, essentially all the preparing and skill grinding becomes an exercise in futility. Your favourite sidequest moments become devoid of meaning when you can't differentiate between a main story and a paltry fetch quest. To quote the Biblical Solomon, everything was meaningless.
Likewise, an over emphasis on the main story can quickly burn out a player. That's why Final Fantasy 13 and it's talk of Cocoon and Pulse and Skyrim's world destroying dragon Alduin just don't provide any meaningful pull on the player. Instead we are drawn to a face, we're not as moved by the moon crushing apocalypse in Majora's Mask as we are the individual stories that are coming to an end. For pete's sake, I would move heaven and earth to save Termina's helplessly devoted mail man before I would lift a finger to stop Alduin.
Dragons aren't so tough
It's only in the balancing act of the monumental and the mundane that a game becomes great.
Games like Deus Ex: Human Revolution perfectly straddle the line between epic saving the world and personal side stories.
These distractions seem arbitrary but a player's motivation to keep playing can hinge on these minor details. Because ultimately, I could care less about saving the wooden NPC's living in the world of Deus Ex, but I would give every drop of blood in my augmented body to save my pilot Farida when she crash lands on route to a mission. It's not about morality, I don't care about killing mercs or the pacifist achievement, I don't care about the objective, heck I don't even care about the future of this world. But my pilot, my sassy friend who has become a convincing and nuanced character, is willing to sacrifice her life for my mission.
"Screw the mission," I yelled at the TV. "I will restart as many times as it takes to save you."
That's a sign of good narrative motivation.