We study the asymptotic growth of the eigenvalues of the Laplace-Beltrami operator on singular Riemannian manifolds, where all geometrical invariants appearing in classical spectral asymptotics are unbounded, and the total volume can be infinite. Under suitable assumptions on the curvature blow-up, we show how the singularity influences the Weyl's asymptotics and the localization of the eigenfunctions for large frequencies. Our main motivation comes from the construction of singular Riemannian metrics with prescribed non-classical Weyl's law. Namely, for any non-decreasing slowly varying function $\upsilon$ (possibly unbounded) we construct a singular Riemannian structure whose spectrum is discrete and satisfies \[ N(\lambda) \sim \frac{\omega_n}{(2\pi)^n} \lambda^{n/2} \upsilon(\lambda). \] This result can be seen as the asymptotic counterpart of the celebrated result of Y. Colin de Verdière fixing a finite part of the spectrum. A key tool in our arguments is a universal estimate for the remainder of the heat trace on Riemannian manifolds, which is of independent interest.