No question, it's a fine line . . . you need to be pragmatic and ask "is writing and maintaining this test going to save me more time than it costs?" Over several years, the maintenance of the tests themselves becomes a huge cost, which is something the TDD guys don't seem to talk about much. (My turn for an overly-cynical guess: since many of them are consultants as you've pointed out, they don't hang around with the same code and the same tests for 7 years, so they don't necessarily see how it really plays out). "Bad tests" are actually a huge net negative for development.
If the test is testing something (like a getter or setter) with basically no chance of breaking, then it's a waste of time. If the test is likely to be fragile or non-deterministic, it's a waste of time. If the test is just too hard to write, and it's not too hard to just test by hand, then automating it is probably a waste of time and you should just QA it by hand every so often.
Finding the right balance tends to come back to the old experience and skill thing: you need to have some intuition about which tests will give you the most value (because that part needs to be rock-solid, or because it's hard to get right, or because it's high-change) and which tests need to be thrown away or never written because they aren't worth it.
Taking any development process too far tends to work out poorly, and taking any metric (like test coverage) too seriously is always a bad idea. That said, I've rarely seen unit testing taken way too far; not testing enough and ending up with buggy, regression-riddled software is a far more common failure mode.