Transactions are limited to a weight of 400.000. Look it up here:
Defined as constant here: https://github.com/bitcoin/bitcoin/blob/3c098a8aa0780009c11b66b1a5d488a928629ebf/src/policy/policy.h#L24.
And validty-check here: https://github.com/bitcoin/bitcoin/blob/3c098a8aa0780009c11b66b1a5d488a928629ebf/src/net_processing.cpp#L661-L665
Since the weight of a non-segwit transaction is 4 times its size, this leads to a maximum size of 100.000 byte (100kb) for valid transactions.
Note the comment:
// Ignore big transactions, to avoid a
// send-big-orphans memory exhaustion attack.
However, if you were to remove that code, it is still possible for it to be mined since it doesn't violate the protocol rules. If most miner SPV mines, then they probably don't use Bitcoin Core directly or modify the codes slightly. Of course, such transactions are unlikely to appear but they are valid, if they can get into a block which doesn't make the transaction invalid in the first place. It has happened before and I don't think any major changes happened.
The second link is for oprhan transactions, which isn't applicable to most transactions.