For nearly three centuries this was considered the northern boundary of civilization. To the south was the Roman Empire and to the north were the barbarians. That description seems a bit harsh but considering they didn’t produce whiskey for nearly a century it might be fitting.
In truth there was little chance of a large scale invasion from the northern tribes. They had the terrain, and the will necessary to keep the Romans out, but not the manpower or the technology to drive them back. The wall instead served as protection for the locals against raiding parties and large scale thievery that would have significantly hurt the standard of living of those to the south. Historians suggest that people were allowed to pass through the wall to go to markets but were generally escorted within the reaches of the empire lest they try to instigate any sort of trouble.
In the end the wall wasn’t abandoned because it was overtaken, but because the empire crumbled from within. Once Rome no longer had the wealth to pay their armies, the local commanders were mostly left to fend for themselves. Since the empire could no longer provide resources or protection to their former citizens, they were effectively the same as the people just up the road and as such new local alliances had to be established.
The wall became no more than a feature of the landscape because the trade and productivity it was there to protect had ceased to exist.