The cognitive load required up-front is big, and I think that puts many off who've never felt the benefits. But it's very rewarding to be able to define a clear set of tests and then have the confidence that when the lights go green that your mission is accomplished (less refactoring). In being forced to write tests first (and endure the cognitive load), I regularly find edge-cases and requirements not originally spelt out, and so avoid having to later re-write having discovered them later.
Much like many things (including "Agile" at the OP mentions), when applied pragmatically I think TDD is an awesome principle to adopt.