I've only seen it done one time. A guy in his 50s was really tired of work yet did not have the assets to retire early. He arranged for a six-month sabbatical. To my knowledge it was the first one his employer had allowed. He was pretty well liked by those in the organization, people from all levels. At first, I think many (though not all) felt he just needed to get away and were ok with it.
Since the plan was for him to come back after the six months off, they did not hire a replacement because when the guy came back, they would have an extra person. Once he was on the sabbatical, part of what he normally did just didn't get done, much of what he had been doing was scattered out for other people to do. The sentiment to his "time away" turned more and more sour as time went on. A few months after he got back, he was replaced and out of a job. Was not a great time to be looking for a job, had a kid in college as well.
I think how a sabbatical works out would depend somewhat on the size of the company. Those larger companies where overstaffing isn't uncommon would seem to work better. A smaller outfit, where they run a tighter ship, it could turn out poorly.