Almost never.

In fact, it was rare for even one of the ships to go down.

Naval battles between cannon armed wooden sailing ships usually resulted in ships being captured, rather than sunk.

Cannon balls rarely shot holes in ships below the water line. Cannon balls striking the water either bounce up like a skipping stone, or decelerate rapidly.

Those few shots which made holes "between wind and water", that is below the water line because the ship was heeling over, so the area below the water line was exposed (but would be underwater when the ship was on the other tack) could usually be patched.

Naval battles were won when cannon fire killed enough of the other ship's crew, and / or disabled the guns, so the ship could no longer fight and had to surrender. Or the enemy ship would be boarded and taken by storm.

Some ships caught fire, or blew up and sunk. A few were sunk from cannon fire, but most were captured, repaired, and "bought into service" with the victorious navy.

Galleys, in the Mediterranean, which rammed each other were more likely to sink ships in battle than cannon armed square riggers.

Until the era of exploding shells, cannon were not generally fatal to ships (though very fatal to ship's crews).