It was mentioned a few times before but Grim Fandango was a lot of fun. (pretty decent length too)
One that was only mentioned briefly was The Longest Journey.
My g/f LOVED that game. It is a really long adventure game that'll keep you occupied for some time. It has a really good story and a female main character that my g/f related to very well. It makes you want to play all the time!
One that was not mentioned: Sanitarium. (yes about a nutcase
)
Very good story, a little shorter that than TLJ but very well thought out. When you finish this one, let it rest for a month or so and play it again... It becomes even better!
Others:
Discworld noir: Like the title sounds : a dark setting, a little surreal
Syberia (as mentioned.. story is a little on the short side, but that just makes you hungry for part two, currently in production)
Blade runner - like the movie. decent game