Pulling the tank on level ground isn't an issue with respect to size and many of the tanks have handles that can be attached to the tow ball on your truck. Where sizes comes in is if any lifting of the full tank is involved. As I've mentioned in other threads some of the dump stations have a concrete curb around them that might require you to lift one end of the tank up to bring it over that curb for dumping. Each gallon of water (not counting waste) weights 8.3 lbs so a 40 gallon tank weights 332 lbs. If you have to lift one end of the tank up over that curb it means lifting 100+ lbs. I'm a little, old guy so handling that size tank is not something I'd want to do (I have a 20 gallon tank which is about my limited as far as lifting).
That said just because a tank can hold 40 gallons doesn't mean you have to fill it to 40 gallons. The dump hose on these tanks is relatively short (mine is about 3 ~ 4 feet). If that's not enough to get from the tank to the dump hole in the ground you could carry your regular dump hose with you so you don't have to lift one end of the tank over the curb to empty it.
The weight issue is one factor to consider.