Minerva Metastasis is the best HL2 mod I have ever played. It was made by Adam Foster, the guy who now works at valve. Valve liked this mod a bit, and eventually asked Adam to join...
Research and Development is an excellent first person puzzle solver.
Strider Mountain is a good, long mod. It has good custom weapons and such. The only bad thing about this one is, the cutscene animations are third rate, but I believe that this was the first mod released by whoever made it.
Missing Information is another good mod. The developers took what was supposed to be the original HL2 storyline from the book, Raising the Bar, and made a mod to reflect that storyline.
Eye of the Storm looks epic, but they have only released episode one of it, and it was short. This mod is the sequel to the Ravenholm mod, which imo was not that great.
Joutemaa, or something really similar to that spelling, also looks epic, but the episode one that they have now brings new meaning to the word SHORT.
Perfect Dark: Source might have been epic if they hadn't cancelled it. I believe the same team is now going to remake perfect dark with the third cryengine.
Black Mesa and Operation Mesa are two epic mods to watch out for, as they will be complete remakes of HL1 and Opposing Force, respectively, using the source engine. Black Mesa is supposed to be released near the end of this year. The release date has probably changed however.
All of the above mentioned mods are FREE as well.