Max INT Identity Value Reached (DBCC CheckIdent)

November 4, 2008 by Michelle Ufford
Filed under: SQL Tips, T-SQL Scripts 

One of my colleagues shared the following experience.

Recently, the identity column on one of his tables reached the maximum value allowable for an INT data type (2,147,483,647). Obviously, this caused all sorts of errors, as the application was unable to insert records into the table. This is a customer-facing table, so the issue needed to be resolved quickly. Converting the column to a BIGINT would require a significant amount of down-time. The solution? Reseed the identity column to -2,147,483,648 and have the values count upward toward zero. This took only seconds and the app was back up immediately. This is, of course, a temporary solution; it allows the team to schedule a more convenient time to resolve the issue.

Here’s how to do this:

DBCC CHECKIDENT ('tableName', RESEED, -2147483648);

Thanks, Peter, for sharing this tip!

Tags:

Comments

2 Comments on Max INT Identity Value Reached (DBCC CheckIdent)

  1. anonymous on Wed, 12th Nov 2008 3:52 pm

    2. Actually, that would be “count upward toward zero”

  2. Michelle Ufford on Wed, 12th Nov 2008 3:57 pm

    3. You’re absolutely correct! Thanks for pointing that out, I’ll update the blog post now. :)

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.