I can think of several games that I never expected anything from but ended up loving.
Half-Life 2 - Having bought the orange box on steam a couple of years ago solely to play portal, my friends kept bugging me to play some of the other stuff on it too. I'd never really heard of the half-life games before and wasn't that interested at that time in playing TF2 (how wrong was I) I eventually got bored enough and played HL2. WOW. I loved it, and couldn't put it down for weeks, though I probably shouldn't have been playing through ravenholm at 2am in the dark on my own. *nightmares*
Soul Caliber 2 - picked up a copy of this a month ago for my gamecube, and haven't stop playing since. The gameplay is smooth, though sometimes repetitive but the different character options keep me coming back for more each time. Also is a great 2player game.
FFX-2 - I had never really touched any FF games before this, I had friends who played them, but as a non console owner at the time, I had never had the chance to get involved in a real RPG. Everyone slams this game for being 'girly' but I think that it works really well and the story really dragged me in.
The Legend of Zelda: Oracle of Ages - my first Zelda game. My Dad bought it for my birthday when I was ten, and I never really played it until I was in my teens. I got stuck very early on in a early dungeon, but once I got past that, I discovered a really wonderful game with excellent puzzles and twists that kept me hooked for months. I have now played every Zelda game I can get my hands on and I wouldn't have discovered the amazing series for ages without it.
Lots and lots of older games from previous generations of consoles - As I can't afford a PS3/XBox/Wii, I have been slowly collecting older generation consoles and have been diving into gamings back catalog for years and discovering forgotten gems along the way. Some of my favourites have been, Fire Emblem, Summon Night, Star Fox, anything Point and Click for the PC, and pretty much everything that ever came out on the N64.
Wow, this is quite a long post...