depending on what models you use 512px (for really small objects even smaller textures are enough) to 2048px should work fine.

4096px is really big and I won't recommend to use them a lot. They might be nice for high detail terrains but you should be careful
with them since textures which are this big eat a lot of memory and make the game much slower.

8192px... that's a texture size I would never use. Simply because it's just overkill and memory waste.
(a 8192x8192px texture has the same size as 64 1024x1024px textures).

But if I will ever need a detailed texture like this I would use a smaller sized texture with decent detail mapping.

And the maximum texture resolution (I searced a bit) seems to be 4096px on DX9.
However, I'm not sure if this really is true.