Implementing limited inventory requires an altered mission design to not become tedious quickly.
Bethesda included a lot of (rather similar) linear caves/crypts/dungeons of medium length in Skyrim. To avoid having the player backtrack all the way to the entrance at the end of the crawl, they often implemented sort of a shortcut that is only usable from the inside - mostly eliminating any backtracking.
Most often elevation or unpickable doors with a lever on the inside where used to block that shortcuts when coming in and make them available when on the way out. On a few occasions they even had a deity teleport the player to the outside after completing a quest.
TDM is no Skyrim, but backtracking almost never is a thing, the player likes.
In most TDM missions, i had to visit most coridors multiple times. That experience changed my playstyle from trying to ghost through to blackjacking everyone (including sleeping people), so i can move faster the second time i have to visit a room.
One way to maybe make inventory limits a fun game mechanic might be to allow the player to leave loot in any place like already possible with other movables. AI would somehow react to finding piles of loot as it already sometimes react to finding some loot items missing. The player could have a loot bag wich can be shouldered like a body when filled with enough stuff. That bag could be dropped at any place (like a body) and the player would make some distinctively satisfying noise of expensive gem encrusted stuff clunking against eachother when moving while carrying a filled loot bag (with the noise being louder if the bag is not shouldered).
In mansion missions without an accessible perimeter, the player could just drop stuff out of windows into the garden and pretend to collect it on the way out (after ending the mission).
There also really are missions, where the loot is a bit excessive. But looting never gets old. I never had the feeling of tediousness when looting the shelf full of golden plates. There certainly is a limit to the amount of lootable items, where exceeding it would make looting boring. But i never saw that reached.
Even in Thief 4 the boringness did not came from the loot itself but from having to endure the same animation over and over. And they really tried hard to make looting seem as pointless as possible by mostly placing stuff as shiny loot that barely makes a coin when hauled to a fence. Looting still was fun even in Thief 4 (we will see whether it still is fun in Thief 5, where i would expect them to feature literal piles of crap marked as loot).
So having a reasonable amount of loot is fine. But don't go as far as in Styx where i always had the feeling that something is missing because you could only loot that special coins and equippment. Also, players not interesting in looting could just ignore it if looting is not one of the mission objectives.
It also feels rather odd when i see on the debriefing that i got all the loot (there are only few such missions though). I do search rooms for loot, but don't expect to find it all. It somehow adds to the feeling of a more alive world, that there is some minor stuff hidden in random places wich keeps beeing hard to find even when searching all the rooms carefully.