It might be worth noting that projective $\mathcal O_X$-modules rarely exist as soon as you leave the affine world. For instance $\mathcal O_{\mathbb P^1_{\mathbb C}}$ is not projective or even the surjective image of a projective.

But you asked about an affine scheme, so we are OK, but perhaps then we can just restrict to free resolutions.

Perhaps the most obvious reason responsible for a module not being free is torsion and perhaps one can have a geometric intuition about those.

The geometric intuition about torsion is that it is supported on a proper (meaning not the entire) subscheme, kind of like the structure sheaf of a subscheme, or several copies $\oplus$-ed together, or doing this for various subschemes and combining them. However, resolutions are about replacing sections by other ones, in the case of torsions, lifting them to the ambient scheme. It might be only possible to do locally, but that's why we have sheaves. For example for the structure sheaf of a subscheme the natural first step is to lift the sections to the structure sheaf and continue with the ideal sheaf.

Ideal sheaves are typically not free, but for different reasons. If the ambient scheme is reduced and irreducible, then ideal sheaves are torsion-free, but that does not mean free in general. For example the ideal sheaf of a subscheme of codimension at least $2$ needs at least two generators along the subscheme it defines, but it is isomorphic to the structure sheaf everywhere else, so in particular its rank is 1. The most meaningful resolution of an ideal sheaf is basically to take as many free elements as the minimal number of generators needed for the point that needs the most. In other words the first step of the resolution of an ideal sheaf is about how many functions can define the corresponding subscheme. Then the next one is about the relations among these functions and so on and on.

I would say it is clear that the farther one goes in the resolution it must be harder and harder to give meaning to the next step. So, perhaps the best is to think about it recursively; every new syzygy is the first syzygy of the previous one. That we might have some vague understanding. And the good news is, as long as the projective dimension of our module is finite, these sheaves will be getting nicer and nicer until one of them will be free already.

It might be worth noting that projective $\mathcal O_X$-modules rarely exist as soon as you leave the affine world. For instance $\mathcal O_{\mathbb P^1_{\mathbb C}}$ is not projective or even the surjective image of a projective.

But you asked about an affine scheme, so we are OK, but perhaps then we can just restrict to free resolutions.

Perhaps the most obvious reason responsible for a module not being free is torsion and perhaps one can have a geometric intuition about those.

The geometric intuition about torsion is that it is supported on a proper (meaning not the entire) subscheme, kind of like the structure sheaf of a subscheme, or several copies $\oplus$-ed together, or doing this for various subschemes and combining them. However, resolutions are about replacing sections by other ones, and in the case of torsions, lifting them to the ambient scheme. It might be only possible to do locally, but that's why we have sheaves. For example for the structure sheaf of a subscheme the natural first step is to lift the sections to the structure sheaf and continue with the ideal sheaf.

Ideal sheaves are typically not free, but for different reasons. If the ambient scheme is reduced and irreducible, then ideal sheaves are torsion-free, but that does not mean free in general. For example the ideal sheaf of a subscheme of codimension at least $2$ needs at least two generators along the subscheme it defines, but it is isomorphic to the structure sheaf everywhere else, so in particular its rank is 1. The most meaningful resolution of an ideal sheaf is basically to take as many free elements as the minimal number of generators needed for the point that needs the most. In other words the first step of the resolution of an ideal sheaf is about how many functions can define the corresponding subscheme. Then the next one is about the relations among these functions and so on and on.

I would say it is clear that the farther one goes in the resolution it must be harder and harder to give meaning to the next step. So, perhaps the best is to think about it recursively; every new syzygy is the first syzygy of the previous one. That we might have some vague understanding. And the good news is, as long as the projective dimension of our module is finite, these sheaves will be getting nicer and nicer until one of them will be free already.