I can't come up with a situation where I would need it.
One case (taken from Constraint Logic Programming using Eclipse) is an implementation of not/1:
:- op(900, fy, not).
not Q :- Q, !, fail.
not _ .
If Q succeeds, the cut (!) causes the second not clause to be discarded, and the fail ensures a negative result. If Q fails, then the second not clause fires first.
Elegant systems provide false/0 as a declarative synonym for the imperative "fail/0". An example where it is useful is when you manually want to force backtracking for side-effects, like:
?- between(1,3,N), format("line ~w\n", [N]), false.
line 1
line 2
line 3
Instead of false/0, you can also use any goal that fails, for example a bit shorter:
?- between(1,3,N), format("line ~w\n", [N]), 0=1.
line 1
line 2
line 3
Thus, false/0 is not strictly needed but quite nice.
Another use for fail is to force backtracking through alternatives when using predicates with side effects:
writeall(X) :- member(A,X), write(A), fail.
writeall(_).
Some people might not consider this particularly good programming style though. :)