I don't think most parks or campgrounds have an overall length limit. Individual sites often have various limits based on topography and trees and whatnot, and obviously a short RV can fit into a long site but not vice-versa. The longer you go, the greater the number of sites you can't fit in, and sometimes the more likely that they're all reserved when you wish to camp somewhere. (If only two sites in the campground are suitable, it's much more probable that they were both reserved than it is that all fifteen that a less large RV would fit in are taken simultaneously.)
I have a 32' (overall) class C. There are certainly a number of sites in Vermont state parks that I wouldn't fit in, at least not well enough to be practical. There are also quite a few sites I do fit in. I've also had some occasional minor challenges at private campgrounds during busy times; planning further in advance and getting reservations earlier would prevent such issues.
On the whole, I don't find this size to be a big detriment for getting sties. However, to pretend that size can never be an issue is just plain silly. For that matter, there are a good many camp sites in the VT state parks that you can't get into with any vehicle or trailer—they're walk-in only tent sites. There are others where much larger than a pop-up or a teardrop or other small trailer won't work.
The width of slides when deployed can occasionally also be a constraint. (I have no slides, so that part is easy for me to figure.)
I would guess that the people with 40' RVs that never experience any difficulty in finding sites don't tend to stay at the sorts of campgrounds that often have small sites. You really need to figure what sort of camping you like to do. Smaller sites often tend to go with more primitive campgrounds and fewer amenities (and hence often lower prices).