Max INT Identity Value Reached (DBCC CheckIdent)

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!

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged . Bookmark the permalink.

5 Responses to Max INT Identity Value Reached (DBCC CheckIdent)

  1. anonymous says:

    Actually, that would be “count upward toward zero”

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

  3. Pingback: Identity Columns: Are You Nearing The Limits? « SQL Fool

  4. I did not know about Reseed, but it is really useful in this situation to alleviate the DB Full problem.

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>