Any of the Jedi Knight games. There's always a point when you get too bogged down in fighting enemy Jedi which, whilst fun, provides a lot less opportunity for messing around than slaughtering Stormtroopers by the hundred. In fact, if I remember correctly, there was all of five Stormtroopers in the last level of Academy, and something like 30 Reborn.
Arcanum: Of Steamworks and Magick Obscura. Great character creator with lots of wonderful options, wonderfully put together first part of the game... but there's a point when you need to level a lot to be able to continue the main quest, and you get all of about six sidequests, so you're eventually forced to wander around the world map trying to find random encounters. The part of the game after that is also fantastic, but it's too much of a pain to get there. (That said, if you're a fan of Baldur's Gate and early Fallout-style RPGs, try this one out. One of the best sadly obscures examples of the genre. Goes for $5.99 on GoG)
Morrowind. Every time I go in wide-eyed and expecting to live an amazing experience... then I remember how the combat is and go away crying like a little girl.
As much as this is going to cast a shadow on my "hardcore gamer cred", even if such a thing actually existed and/or mattered, The Sims. Always intend to come in and get my Sims to greatness, and three hours later the entirety of my family is passing out because I force them to grind skills rather than sleep and eat. Then, when I get into a routine that works I realize I'm doing what I do in real life in fake life, and realize I might as well go grind cooking skills for myself.
The opposite to what we're discussing applies to both KotOR games. I find the part on Taris in the first one to be incredibly boring, there's next to no plot going on, you're just fighting the same generic mooks over and over, and the stuff that happens on it becomes almost completely irrelevant. Same with Peragus and Telos in the second: Peragus has way too much combat, Telos has way too much plot that becomes ultimately irrelevant followed by another section of way too much combat. That said, once you get past those bits, both games are stelar, even with KotOR II being as unfinished as it is.