This answer applies to the US, perhaps elsewhere, but I have no explicit knowledge.
A three year conditional contract that doesn't result in tenure at the end is usually followed by another. Tenure will seldom be awarded after the first three year contract but if no progress is made there might not be a second try. After six years a decision is made. If the candidate fails to achieve tenure then, they will usually get a terminal one year contract in which they are expected to find employment elsewhere.
It is possible that someone fails to gain tenure simply because the conditions at the university have changed and they can not commit to a long term hire. Perhaps a couple of older faculty were expected to retire but did not. Letters from department chairs and deans explaining this will help mitigate the damage for the future and could even result in strong recommendations to other places. This is usually rare, but in difficult economic conditions (COVID) it happens.
Depending on the university that fails to grant tenure, the candidate might have a hard time getting employed, especially at one with a more or less equal reputation. But, the US has a lot of colleges and universities and failing to get tenure for research might not have a lot of effect for a teaching college, many of which are excellent.
Some places will offer support for candidates along the way, coaching them in the important aspects at that university.