I was always under the impression that one should turn off the Image stabilization when using a tripod but when I see lenses with long focal length (300mm, 600mm etc...) comes with IS on them. Obviously these lenses are too heavy to be used without a tripod so I believe that there should be a focal length beyond which it becomes necessary( or acceptable ) to use IS along with a tripod.
What would that focal length be?
It is not so much the focal length as it is the individual design of each lens and IS system. Some of the earliest IS lenses didn't do well on tripods, and that is where the idea to "always" turn off IS when using a tripod came from. It really is lens specific.
Most of the Canon Super Telephoto lenses have IS Systems designed to be used with a tripod. The "II" series even has some lenses with an IS mode that is specifically designed to counteract vibration from the mirror so that mirror lockup, and the accompanying delay, does not have to be used.
While very rarely handheld, supertelephotos are also used on monopods, so the existence of IS on these lenses does not necessarily indicate that IS is needed when using a tripod.
With a sturdy enough tripod, there is no focal length long enough that IS is a requirement. So the question really is what combination of tripod wobblyness and focal length requires IS to be turned on. But without a standard for wobblyness your question as it stands cannot be answered.
It isn't so much a question of what focal length it becomes necessary as, as it is what situations it is helpful for. If you are running in to issues with motion of the camera body is causing issues, turn it on.
Quick handheld shots are possible at 300mm. (I've even done reasonably smooth video at 300mm handheld with IS.) Shooting from a steady position could be feasible at 600mm too I would think if you are really good and have your rig mounted to something like a shoulder mount. IS is a dual edged beast since it works against movement, but it also works against movement when on a tripod.
In general, I don't turn on IS unless there is a stabilization problem where it would be helpful. Normally with a tripod it isn't, but if there is a vibration it can deal with and has a mode that doesn't interfere with any motion I need to make, then it can be useful.
Like rumblestilskin would say "magic always comes at a cost" and that's how it is with IS, too. The gyro will constantly make adjustments, making an image less sharp than it would without IS if there is no motion. You are better off stabilizing your tripod which is the purpose of using the tripod in the first place, instead of handheld where the IS is very usable. Instead of IS on a tripod you can use mirror lockup, and remote or timed trigger.
Of course the wind or ground vibrations (on top a skyscraper you might have trouble, or if a train passes) could be working against you. But then I question of the IS can handle those types of motion anyway, on the typical lenses for handheld scenarios. Special versions can exist.
