Cons:
"it would be a hit financially, we would be ok but would have to sacrifice big holidays etc"
School holidays would change with a SAHD arranging lots of days out and activities and a 'big holiday' might not really matter. What would matter is the foreseeable big expenses - would you still be OK if e.g. the boiler needs repaired? 'Being OK' needs there to be some savings to take care of the unexpected-but-essential.
"His car is a work car so we would be without a car which is incredibly handy."
Only you can know how it would be without a car. Presumably you get to work by other means, heavy groceries can be delivered, lifts for the kids can be bartered for after-school care with other parents by a SAHD, an occasional taxi for travelling at night. It's doable, you don't seem to see a car as a necessity.
"His pension would take a huge hit as we couldn't afford to contribute to it if he isn't working."
The long-term issue of the pension is massive, and even if that were the only con, it would make me pause for thought.
"I worry I may become resentful as kids are at school full time and he isn't the best cleaner so I imagine there would be a lot of him playing his game all day."
There's three point in there, so I'm going to take them separately:
1 - "he isn't the best cleaner"
For the cleaning, you could take the view that he will get better with practice. But I'm guessing his standards are lower and you would foresee having to do the actual cleaning yourself after he's waved a damp cloth in the direction of the dirt? That's going to cause massive resentment right there.
2 - "I imagine there would be a lot of him playing his game all day."
Fuck. Right. Off! I'd take a sledgehammer to the console if he pulled that stunt. But what you posting that tells me, is that you do not trust him to pull his weight for the household if he gives up paid work, probably because he does not see domestic work as his responsibility, and therefore would shuck it off onto you.
3 - "I worry I may become resentful as kids are at school full time"
Your resentment would be because you foresee yourself holding down a job AND having to do the Second Shift in the home because he's not going to take the housework off your shoulders AND behave like a slacker teen on his console.
This is as massive as the pension problem, frankly. Plus it effectively wipes out the practical Pros from your list, because if he's glued to his console all day he's not going to be an active SAHD is he? He'll be physically present, yes, but is he going to be doing anything more with your two primary-aged children other than sitting them in front of the TV and returning to his console? You might look back on the "chaos trying to sort care for holidays, sicknesses, drop offs etc" fondly! Ditto your current use of after school clubs and holiday clubs
The only Pro left really is that "He wouldn't be as stressed all the time as this always affects everyone in the house." Well, there's a good chance that he wouldn't be as stressed, but equally there's a good chance that you will be considerably more stressed, so the stress levels in the household will not actually reduce.
"But my worry is we will eventually regret it as would sacrifice nicer things in life and worried he would struggle to find another job in a few years when the kids are at high school (or end up being lazy and I would have to nag constantly about housework)"
You describe your jobs as professional, it obviously depends which profession as to how hard it would be to re-enter it in a few years. Would it be possible for him to do some sort of Continuous Professional Development such as a Masters whilst not working in the profession? And would he actually do it, or sit on his console? And you are already foreseeing him becoming (already being?) lazy on the domestic front.
Honestly, the Cons are massive and the only real Pro is that your husband would be less stressed by not working in a job he hates. If that's the only Pro then it would be better addressed by him gritting his teeth while looking for another job. Maybe being prepared to take a lower salary, which would still maintain his pension.
You have your doubts about him being an effective Stay-At-Home-Dad contributing domestic labour and child-rearing to the household, whilst you become the sole earner Working-Outside-Home-Mum. You're not going to be able to resolve those doubts without long, involved, detailed discussions with your husband. It could work. But it might require a huge change in attitude towards the domestic sphere on his part. Those conversations need to be had before you can, jointly, come to a decision. And like I said, they need to be detailed - no crossing of fingers and declarations of 'we'll make it work somehow'. No! 'Somehow' is not a strategy! And the conversations must include you stating clearly 'I will hugely resent you if you treat this as an opportunity to just play on your console all day. HUGELY resent you.'