No, the battery drains all the time. That's the whole point of having a battery backup - if it didn't take any power to maintain the SRAM, you wouldn't need a battery! Having the game in the console doesn't change anything, either (unless the power's on, of course).

The lifespan on the batteries themselves varies. They do have an effective shelf life of 10-15 years, but they can die much quicker than that. There really isn't any way to tell when one's going to crap out on you. The frequency of use might have some small effect, but I really think environmental conditions are the biggest factor.