There are different types of carryovers and they don't all work the same - from your question it looks like you're asking about carryovers from a capital loss - example, from selling stocks at a loss. As you inferred, you cannot claim a net loss of more than $3,000 on your individual tax return if you are not in the business of trading stocks (which it sounds like you are not). So the simple answer to your question is they are carried forward indefinitely.
These credits reduce your taxable income - which can be earned income, but also income from investments, gambling, the taxable portion of social security, etc... So yes, if you have a gain on the sale of your main residence that is in excess of the exclusion ($250K, as you said, or $500K if filing a joint return with your spouse), then the carryover from prior year losses will reduce the taxable income related to this. But I would suggest thinking of it more broadly as reducing your taxable income, whatever makes up that taxable income.
