As others said, it really is something that must be considered on a case-by-case basis. There's no single magic cutoff value where suddenly site are no longer available, generally speaking, but rather the largeer the rig the fewer sites it will fit in, until possibly at some point it's impossible to fit in any site at a particular campground. Even if there are some sites that would fit you, if they all happen to be occupied or reserved it's no help, even if many other sites you can't use are free.
Here in Vermont, a 40 foot motorhome would be quite restrictive for the state campgrounds, many of which were built by the CCC and have not been vastly changed since then. Even my 32' motorhome can require a little flexability as to when and/or where to go camping. In other states and areas it's less frequently a big concern.
It's also worth noting that sometimes--certainly not always--the maximum lenghts stated online for particular sites are extraordinarily conservative and larger rigs can easily fit (and navigate the roads to get to the site). Other times, they are precise and accurate. It can be helpful to call and speak to a ranger or someone familiar with the actual campground. Reservation clerks often don't have detailed local knowledge, particularly if the reservation system is statewide or national.
