The story you are thinking of is

“Return Journey” by J. T. McIntosh
(first published in Galaxy Science Fiction, June 1957; re-printed many times since, e.g. in The Best from “Galaxy” Vol. III, The Road to Science-Fiction 3, Great Science-Fiction Stories, and other 1960--1980s anthologies).

Outline that matches your memories

1. All–male deep-space survey ship
   • The vessel and its charts were damaged while on an outward mission, so the surviving crew have only a rough idea of where the Sun should be.
   • Between stars the men lie in suspended animation.
   • At each system two men are revived first: the captain plus one companion who make a quick landing to see whether the planet is really Earth.
   • If the answer is “no”, the two go back into the freezer, the ship moves on and a fresh pair will do the next landing.

2. Mounting despair and suicides
   • The routine has gone on for centuries.
   • Every failure costs morale; a number of crewmen have killed themselves rather than face another cycle of disappointment.
   • A running discussion in the ship’s log is whether it would be kinder simply to give up.

3. The final landing described in the story
   • This time the captain takes a junior officer down.
   • They discover a planet covered with silent, dust-thick, faintly radioactive ruins – unmistakably the remains of human cities.
   • It is, of course, Earth itself, destroyed long after the survey ship left.

4. Collapse and decision
   • The younger man, realising what they have found, breaks into hysterical laughter and commits suicide on the spot.
   • The captain, after a moment of private grief, returns alone to the ship.
   • In the log he reports merely that the planet is another negative, erases the proof that it was Earth, sets the automatic controls for the next promising star, and leaves a cheerful recorded message: “Better luck next time, lads – maybe the very next one will be home.”

Everything you cite – the frozen crew, the two-man scouting parties, the long succession of failures, the suicides after each disappointment, the discovery of a dust-shrouded ruined Earth, the crewmate who blows his brains out, and the captain who decides to keep the truth from the others and press on – is in McIntosh’s story.