This trope basically comes from a combination of Rule of Cool and Bigger Is Better. An oversized sword is a unique weapon that will stand out from the crowd, and marks its wielder as a person of above-average or even superhuman strength. Many writers like for their protagonist or villain to be as cool and unique as possible. As for its usefulness as a weapon, most people assume that a bigger, heavier weapon will inflict more damage on whatever it hits, provided that you can find a person strong enough to wield it. Force equals mass times acceleration, so if you take two swords of different masses and swing them at a target at the same speed, then the more massive sword will take more energy to accelerate to that speed, and also hit the target with more force. However, people might fail to consider that oftentimes it is more efficient to increase the acceleration of the blade than to increase its mass. By way of analogy, modern rifles using more efficient and powerful smokeless propellants shoot less massive but more aerodynamic bullets at higher velocities than their black powder ancestors, which with the help of rifled gun barrels lets them achieve more accuracy, range, and penetrating power than the large musket balls of yore.
The power source and maximum crew size is also a limiting factor: When you're talking about a modern firearm, the chemical propellant in the cartridge case provides the energy to propel the bullet, while the chamber and barrel must be built to contain and efficiently harness that explosive power. A trigger mechanism connected to a firing pin is all that's needed to activate the chemical reaction, and then all that matters is whether the weapon is aimed properly and supported to control the recoil. When you're talking about a sword, however, the power source is old-fashioned human muscle. Unlike designing a gun cartridge, where if you create a more powerful cartridge then you can design and manufacture a more robust new firearm whose chamber and barrel can handle the increased pressure of a bigger explosion, you cannot create a bigger sword and then design a new, enhanced human from scratch who is big and strong enough to wield it. Unless maybe you're talking about an Artificial Human or robot, but if you can make one of those, then is a sword really the most hi-tech weapon you can equip them with? Also unlike the example of firearms, in which a gun or cannon too big to be fired from the shoulder can still be used as a crew-served weapon mounted on a tripod, gun carriage, or vehicle, a sword has a maximum of one wielder and can't be used if it's too heavy for a single person to swing with their own two hands. Therefore, sword design has to take into account what the average physically fit human warrior is capable of.
Sheer mass is not by itself a good thing; you need to make the sword just heavy enough in the right places to do the job intended, without getting to the point where the mass is just slowing you down and robbing your strike of energy. Generally you can make a blade big in one or two dimensions, but not in all three dimensions at once. Some swords such as the Chinese dadao or Philippine panabas have very short, very broad blades (which allow for a gradual cross-section taper to an acute edge), are more forward-balanced, and have long two-handed grips; this gives them a lot of cutting and chopping power at the cost of short reach and points that aren't ideally shaped for thrusting. Medieval cutting-focused greatswords would often have a long blade that was broad along its entire length (in other words, little profile taper) so it would have reach, cutting power, and a broad point that could still penetrate unarmored or cloth-armored opponents; such greatswords would also have an aggressive distal taper, meaning that the blade was thick near the guard but got progressively thinner towards the tip, keeping it from getting too heavy towards the point and making the tip area more acute-edged for cutting, but also making it not narrow or stiff enough to easily penetrate mail armor or slip through the gaps of plate armor with a thrust. And then there's the rapier, which sacrifices the ability to make powerful cuts for point control, reach, and penetration: The blade is very long (for reach), has less distal taper and a rigid temper (for stiffness), an accute point (for penetration), and a narrow profile (also to help penetration, and to prevent the length or thickness from making it too heavy). There is also more mass in the guard relative to the blade, pushing the center of balance closr to the hand, which makes it easy to manipulate the tip. However, this makes for a center of percussion that's further back from the point, which reduces the range at which you can make effective cuts, and the thick edge section combined with the light blade reduces cutting power. The estoc is even thicker and stiffer than the rapier, making it suited for anti-armor use in a way that the more delicate dueling rapier isn't, but since the cross section is so thick it has practically no cutting ability at all (though you could still break bones or fracture someone's skull with a good whack). Each of these swords, despite their differences in handling, are basically agile and maneuverable weapons because they make compromises. When it comes to length, breadth, and thickness, you can pick any two but have to sacrifice the third if you want it to be a handy weapon, while keeping in mind what kind of edge or point geometry that combination will create. What the BFS does is expand in all three dimensions at once, to the point where the overall weight and balance would become unmanageable.
Giant swords are impractical in the real world because of the Square-Cube Law, which guarantees that a sword that gets too heavy will be slow and cumbersome no matter how strong its wielder is. The sword has to be a relatively small percentage of its wielder's body weight—and indeed a pretty small weight in absolute terms—in order for human muscle power to accelerate and control it at high speed. Even a powerlifter who can bench press over 700 pounds would struggle and fail to use a 15 pound sword for fencing at normal speed, simply because it is exponentially harder to swing a substantial weight around your body at more than arm's length than it is to lift or press the same weight in a straight trajectory towards or away from the body, and whatever you can do with it will be much slower in comparison because a heavier object has more inertia than a light one. Eight pounds is about the absolute limit for what even a six-foot long greatsword can weigh before it becomes too unwieldy for fencing at speed. What's more, wielding a truly giant sword such as Cloud's Buster Sword would require not only Super-Strength, but also a whole set of Required Secondary Powers. An object that large has tremendous inertia, meaning that it is extremely difficult to get it moving from a resting position, and just as hard to stop it or change direction after it gets going. In order to actually exert upon the weapon the minimum force needed to accelerate it to the speed of a normal sword swing, you would have to be able to push off of the ground without your feet slipping out from under you as you step forward, and then the sword would try to yank you forward along with it as soon as it gathered some speed. In order to step and swing with control you would need to artificially increase your body weight—whether by ridiculously heavy armor or some kind of sci-fi gravity belt—and take similar measures for your overall stability and the traction of your shoes. Once you were able to swing it and keep your footing, you would also need Super-Toughness in order for your bones, muscles, and joints to withstand forces that would rip a person's arms off.
Even if you had all these powers for controlling a BFS through a proper swing, a swordfight is not won—much less survived—by relying on one great swing to fell your opponent. You have to consider how long it takes to recover from a missed swing, transition between different guards or stances, and defend against relentless attacks coming from various directions. At best a superman using a giant sword would be rather sluggish and vulnerable to anything nimble enough to avoid his blows, and unless his super strength also came with unlimited stamina, he would quickly become exhausted and lose the ability to either attack or defend himself effectively. It is far more efficient to give a strong person a sword only slightly heavier than the average weapon of its class, so that they can handle it more effortlessly and nimbly while still getting some extra "oomph". There's also the fact that many fictional BFS examples would be quite the opposite of an Absurdly Sharp Blade, being so chunky and having such thick and chisel-like edge geometry that they would not cut through whatever they'd hit so much as crush the material with blunt force. A sword-like object that lacks either the nimbleness or cutting ability of a sword might as well be a club, mace, or ax instead.
Many examples of the BFS that were made in real life are simply not meant for combat. An Early Modern European executioner's sword was designed to chop through a restrained person's neck in one swing, meaning that defense and agility were of no importance. Therefore they were made with long grips for leverage, while their blades were relatively short, forward-balanced, and often without a point. Since they had a practical function, at least these were usually within the outer weight parameters for a fighting sword. In contrast, ceremonial swords had hardly any constraints on their weight and dimesions. For example, King Henry V of England's bearing sword in the Tower of London was exaggeratedly large in order to look impressive from a distance as someone carried it in front of his procession, and the fact that it was too big and heavy for pracical use didn't matter. You could say that this latter category subverts an important part of the trope, because unlike the fictional BFS which is made because there's someone who can wield it effectively, smiths of old were under no illusions that their giant ceremonial swords were useful to anyone as practical weapons.
Speaking of which, manufacturing and materials would also have been a problem. Even today, making an authentic Zweihander replica is more difficult and expensive than making a longsword or arming sword because it requires more steel, there's more material to pound and grind, it's awkward to maneuver around the workshop, and in the case of smaller workshops it may be too large to fit inside the smith's existing appliances such as the forge chamber, quench tank, or annealing oven. All those difficulties would have been greater back in the days of more costly materials, limited power tools, and lack of control or precision in the heat treatment process. Now once we start talking about "anime-sized" weapons, you can see on shows like Man at Arms how hard the problems are for even the most skilled and well-equipped smiths using modern technology; even then, what they produce is often less massive and thick than the fictional version. The people in many historical settings might not have been able to produce a large enough bloom or ingot of more-or-less homogeneous steel to make the blade out of, and forge welding together the blank for a massive Buster Sword out of numerous smaller ingots would have been outrageously labor-intensive at best. Finally, once a certain threshold of mass is reached it becomes doubtful that the sword could survive its own blows. The thickness of the tang is limited to the diameter of handle that a person could wrap their hands around, and a relatively thin tang on a blade weighing triple digits would bend or snap off at the shoulders under the blade's weight and momentum; this would be most likely to happen in any instance of powerful motion being suddenly arrested. Depending on the forces involved, the blade might also buckle under its own weight when it struck something. Modern or futuristic materials would be necessary to make such sword dimensions remotely usable.
Real, practical two-handed blades from Europe such as the German Zweihander and Scottish Claymore weighed around 5-7 pounds. Compare this to about 2.5 pounds for a one-handed arming sword, and 3.5 pounds for a longsword that could be worn at one's side. The two-handed sword had a very long grip, which allowed greater control over the blade than a shorter hilt; where a one-handed blade's weight was held steady by the hand and wrist, the longer hilt of these swords turned it into an arm motion and created leverage through the distance between the hands. Such greatswords were not used just for sheer power, but for a combination of cutting power, reach, leverage, defensive radius, intimidation, and versatility. The large dimensions and inertia of big two-handed swords made it difficult to take full advantage of their capabilities without considerable stamina and skill, which is why the Doppelsoldner who wielded the Zweihander in German mercenary armies earned double pay for their expertise.
