Man, I wish I could re-cultivate that mindset. I don't find any games addictive any more. I'd be surprised if I could find a game I've finished in the Steam collection.
But, going back in time I'd say Thief II or Total Annihilation. Thief II was just crazy when it came out. My computer couldn't handle it at the time, so I befriended some rich people and played it like crazy over at their house. Everything from the environment to the story sucked me in like a vacuum cleaner of love. I replayed it a bunch because of the different ways you could complete missions and move through the levels.
Total Annihilation just blew everything out of the water. It was developed about a mile away from my house and when I was finally able to get it to run on a machine, I peed my pants a little out of tension. While everyone else was mutually tossing salad over shit like Star Craft and Age of Empires (both games I did and still do love to this day), I was jamming an RTS game with nukes, a true 3D environment, essentially unlimited units, completely varied factions, air, land and sea battles, etc. It was an awesome game that really didn't get the attention it deserved. It was very innovative for the time and a massive step forward, especially graphically. I still play it to this day. Nothing beats launching 10 nukes at an enemy base with an armada of 50 cleanup bombers and 100 escort fighters in my eyes. Or sending 150 amphibious tanks across the ocean to provide a front while you maneuver 100+ bombers to strike from the flanks. Great game.