In what spaces does the Bolzano-Weierstrass theorem hold?

A metric space is sequentially compact $\iff$ it has Bolzano Weierstrass property. And, for a metric space, compactness $\iff$ sequential compactness, and hence, the metric space should be compact for the property to hold.
And then, a metric space is compact if and only if it is complete and totally bounded. So, now for an arbitrary metric space, you must decided whether it is complete and it is totally bounded. I guess, the methods would differ from space to space and metric to metric.

Since you were talking about infnite-dimensional space, a closed unit ball in $R^{\infty}$ is not totally bounded.

Then there is another theorem (which I have not yet studied till now. Simmons has described it before, and said we will prove it later) which says that a Banach space is finite dimensional $\iff$ every bounded subspace is totally bounded. So, bounded subspaces of finite dimensional banach spaces are not the place to look for counterexamples.I do not know which other spaces are there to look then.

The closest analogue of the Heine-Borel theorem in arbitrary metric spaces is that a subset is compact iff it's closed and totally bounded. But totally bounded sets pretty obviously have compact closure, being those which have finite covers by $\varepsilon$-balls for every $\varepsilon$, so this isn't much of an improvement. Anyway, it's easy to see that your example sequence in $\ell^2$, or whichever norm you prefer, isn't totally bounded since its elements are pairwise $\sqrt{2}$ apart, so that no $\varepsilon$-ball will cover more than one of them for $\varepsilon<\sqrt{2}$.