How does Gödel Completeness fail in second-order logic?

The property that "every consistent theory has a model" does not hold for second-order logic.

Consider, for example the second-order Peano axioms, which are well known to have only $\mathbb N$ as their model (in standard semantics). Extend the language of the theory with a new constant $c$, and add new axioms $$ c\ne 0 \\ c\ne S0 \\ c\ne SS0 \\ \cdots $$

The extended theory is still consistent -- that is, it cannot prove a contradiction -- because a proof must be finite and can only mention finitely many of the new axioms, and there is a model as long as we only take finitely many of these axioms.

But the extended theory does not have a model, because the model will have to be exactly $\mathbb N$ in order to satisfy the original Peano axioms, but must have a $c$ that is not a numeral in order to satisfy all the new axioms.

Take the language of arithmetic, augmented by a single constant symbol $c$. Now add to the Peano [second-order] axioms the following schema, $0<c$, $s(0)<c$ and so on.

If this theory is consistent, then it should have a model. But second-order Peano has only one model. So it has to be that model. But how will you interpret $c$ there? You can't.

So the theory is inconsistent. So a contradiction should be provable from finitely many axioms. But given any finitely many axioms, you can find some large enough natural number such that interpreting $c$ with that value is okay. So any finitely many axioms from this theory do not prove a contradiction.

So you have a theory without a model, but without inconsistencies. So completeness (and compactness) fail.