An IDENTITY column has reached Max int

Question

An IDENTITY column has reached Max int

WhiteLotus

SSChampion

Points: 12122
More actions
May 29, 2016 at 10:39 pm

#319217

Hi I have issue in my production server , The error is " An IDENTITY column has reached Max Int"
so I run DBCC CHECKIDENT('XYZ',RESEED,0); to reset the value of identity column . ( I am thinking
to change it to BIG INT )
I wonder how do I monitor this as it might happen in the future ..
Any feedback are highly appreciated
thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24399 More actions · Answer 1

WhiteLotus (5/29/2016)
Hi I have issue in my production server , The error is " An IDENTITY column has reached Max Int"
so I run DBCC CHECKIDENT('XYZ',RESEED,0); to reset the value of identity column . ( I am thinking
to change it to BIG INT )
I wonder how do I monitor this as it might happen in the future ..
Any feedback are highly appreciated
thanks

Unless you have truncated the table, you stand a chance of creating duplicate values.

CREATE TABLE #table (

ID INT IDENTITY(1,1) NOT NULL

, DT DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()

);

SET IDENTITY_INSERT #table ON;

INSERT #table (ID) VALUES (20);

SET IDENTITY_INSERT #table OFF;

DBCC CHECKIDENT ('#table', RESEED, 18);

INSERT INTO #table DEFAULT VALUES;

SELECT * FROM #table;

If there is a large amount of data in the table, the change from INT to BIGINT will take some work. The change is not simply a metadata one, so you either

* take the massive perf hit while your column gets an update for every row, or

* create a duplicate table with the correct data size and migrate the data. Then swap the table out once they are in sync.

How to post data/code on a forum to get the best help.

WhiteLotus SSChampion Points: 12122 More actions · Answer 2

Thanks for the quick reply !

I deleted the table first before performing reseed .

I heard if we do TRUNCATE , we don't need to delete and RESEED anymore ?

As truncate will delete and reset the identity back to the original value

Is it correct ?

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

Yes,

Truncate will remove data from Table and reset/initialize the identity value.

WhiteLotus SSChampion Points: 12122 More actions · Answer 4

btw if i run this command :

DBCC CHECKIDENT ('#table', RESEED, -2147483648)

the values count upward toward zero .

My question is if it already reach Zero will it continue counting until 2147483648 ?

Data type INT

Smendle Hall of Fame Points: 3969 More actions · Answer 5

INT value is 2^32 which is 4.294 billion or -2.147 billion to 2.147 billion. So yes it will count up from 0 (once it reaches 0) starting at -2.147billion.

Y.B. SSChampion Points: 11649 More actions · Answer 6

Since your are going to a larger data type I'm pretty sure you can just do something like this and still maintain the seed values.

ALTER TABLE myTable ALTER COLUMN myColumn BIGINT

Just make sure you test this out in a dev environment first!!

SELECT quote FROM brain WHERE original = 1
0 rows returned

WhiteLotus SSChampion Points: 12122 More actions · Answer 7

WhiteLotus

SSChampion

Points: 12122

May 30, 2016 at 6:30 pm

#1882056

Thanks Guys !

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

DesNorton (5/29/2016)
I deleted the table first before performing reseed .

If you were able to, so nonchalantly, just kill all of the data in the table, why did you wait until it got out of hand to kill it? And, truly, is it all just "throw away" data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DesNorton SSC-Insane Points: 24399 More actions · Answer 9

Hi Jeff

Something went awry with your quote. Somehow the OP text was quoted under my name

How to post data/code on a forum to get the best help.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 10

I had this happen about 2 years ago. The apps guy called me and said that the identity had hit its max. The way the app works is it puts a lot of rows into the table daily and they are temporary for other processing. Once the other processing is complete that old data is no longer needed. We delete data older than 7 days in that table. So I simply reset the identity value and all was good again.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

DesNorton (5/30/2016)
Hi Jeff
Something went awry with your quote. Somehow the OP text was quoted under my name

That happens if one waits a bit, someone else posts, and then you hit "QUOTE". The forum software has a bit of a bug in it. Thanks for bringing it to my attention.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)