You can go up to 500gb without any trouble and compared to what you have now it will seem like the Grand Canyon. I would think you could go a lot higher but if you have been using a 40gb then you might not want a very big one.
I know XP will not handle the 3TB and 4TB drives (Not 100% certain about 2TB drives). Considering you are now asking about upgrading a 40GB, A 500GB to 1TB will easily give you way more room, and will easily work with your system. Another thing you could do is just keep the 40GB as the OS drive, and use the new drive as storage.