Why did this happen to the First Order Star Destroyers at the end of "Rise of Skywalker"?

Several things are to be kept in mind.

To start, we are not told how much time has passed between the Emperor's defeat and the following post-Exegol scenes. We mostly infer that it was not that much because Rey still looks exhausted and dirty. Long term exhaustion after that battle could be easily explained, but why she seems to still be wearing the same battle-stained outfit is less explicable (she's cleaned it up by the time she arrives on Tatooine, though).

Second, the entire movie made it clear that it was possible to traverse virtually any distance in a matter of seconds, even without a mystical Force connection. The Rebel fleet could have easily moved on to other systems without needing any appreciable amount of time to pass. There was also a significant delay between when Poe was expecting reinforcements to arrive versus when they actually did. And it was never said that everyone who responded to the call to fight against the Emperor did so by arriving at Exegol. Remember, "there are more of us than there are of them". The idea to attack the cannons was also thought up before they left for Exegol, to my recollection. So even if we buy that the scenes you are concerned about occurred shortly after the fight on Exegol, there was still time and manpower for local rebellions to occur.

Indeed, for those systems under threat from the already-departed ships, there is strong incentive to deal with the immediately obvious threat from Palpatine, and potentially limited options for bypassing it (they might just be shot down, have their systems jammed to prevent safe jumps to light speed, etc.).

Moreover, throughout the series we have seen that these ships are extremely weak to direct attacks. They seem wholly reliant on the intimidation effect: that resistance will result in subjugation or, in this case, wholesale obliteration. Their defenses are limited almost entirely to TIE fighters and mounted surface cannons, while the rebels use a wide variety of ships and attacks. They also provide a target rich environment: the rebels are explicitly trying to hit the giant ship, while the giant ships and their TIE fighters are trying to avoid hitting themselves, which gets difficult when the rebel ships are so much smaller and so close.

And they have critical weak points. Clearly their most clever and defense-minded engineers were working on the Death Star to cut its weakness to a single exhaust port, rather than, say, an easily located load-bearing signal tower or obvious explosion-prone weaponry. And it apparently couldn't be blown up by a small payload of bombs on a relatively miniscule section of the surface. We also see Finn take out an entire destroyer with a single shot from just one of its own mounted canons. And how he only managed to take out a chunk of the ship when blowing up the tower with an explosion from 500 feet away, instead of just taking out the entire ship, is nigh inexplicable.

In all honesty, the ships appear to be made of explodium, so all you have to do is get past some TIE fighters and they're pretty much doomed.