You may be interested in these posts I wrote way back (about an year ago). They deal with the same issue arising out of working with integration to a UNIX system (based on a practical experience I had):

• UNIX timestamp and the Year 2038 problem
• Converting UNIX timestamp (BIGINT) to DATETIME value using DATEADD function

Vadivel, you forgot the negative values. So in fact number of years should be doubled if you include negatives :)

Sometimes, altering the column may not be a quick option. Especially if the database needs to be up 24x7 and if you don't have a proper maintenance window. You may need to drop primary-keys/indexes or if the table is replicated you may have another set of troubles.

So a quick fix when this happens could be to RESEED the identity value to the biggest negative number supported by that data type (most well-written applications may not break with NEGATIVE identity values). This will give you some breathing space to 'think' properly. It is always a challenge when something goes wrong on the production server. The application stops working, users start complaining, phones start ringing and you really need a 'first-aide' available immediately to put the application back on-line.

Also, it may be worth considering that when rows are deleted and transactions are rolled back, we loose identity values. So a table may have 10K records, but the highest identity value may be 500K. So they get eaten up much quicker than we would assume :-)

Madhivanan,

Actually I have mentioned that "(A) Considering only positive numbers". Yeah once we consider Negative values as well then its gonna be double :)

Disclaimer: I am relatively new to this site. Still trying to figure out the various options. So pls bare with me if there is any delay in my response :)

Jacob,

I agree. Actually intention of this post was not to show the readers the various ways of overcoming INT overflow. My intention was to show how long a BIGINT can survive before it reaches the max limit.

Nakul,

Thanks for those links. Would check it out.

@Vadivel,

Absolutely. I agree with you. My intention was to add some related stuff that may be helpful to people reading this thread.

Please note that the "You" in my comment did not refer to "YOU" (author of the post) :-). It was more referring to the reader of the post. So texts like "You may need to drop primary-keys/indexes or if the table is replicated you may have another set of troubles." really meant "A user who intend to alter the data type of a column may need to drop primary.......blah blah" etc. So the 'you" in my posts always intended to addresses the readers. (Time to change my writing style :-)

I found your post really interesting. We do have many tables having BIGINT data types. I do even have a script/application that scans all the tables, checks for the values and alerts us if we are reaching the maximum limit of any columns in the various databases we have (we have columns that are tinyint, smallint, int, and big int etc). I very much knew the maximum limit of BIGINT, but never tried to do the math to find how long we can go till we hit the limit. We always considered 'one day' we will hit the limit.

I just realized that it may be my daughter's grand children who may need to worry about the BIGINT columns that I created a few years ago.

Looking forward to see more posts...

"You" in my comment did not refer to "YOU" (author of the post) :-). It was more referring to the reader of the post. So texts like "You may need to drop primary-keys/indexes or if the table is replicated you may have another set of troubles." really meant "A user who intend to alter the data type of a column may need to drop primary.......

Although it may sound snobbish, the correct and succint way would be to say "One may need to drop primary-keys/indexes..."

My 'two pennies' as an Enlgishman :)

Agreed. Thanks

You forgot to factor in leap years in your calculations! :-)

It is just 365.24 days * 24 hours * 60 minutes * 60 seconds = 31,556,736.

This post reminds me my post on Time to write the result of 10^10^10