IDENT_CURRENT is out of sync

Question

IDENT_CURRENT is out of sync

rVadim

Hall of Fame

Points: 3969
More actions
May 14, 2019 at 6:33 pm
Go to Answer
#3642058
I have an AuditTrail table with IDENTITY column defined as:
```
[AuditTrailID] [int] NOT NULL IDENTITY(1, 1) NOT FOR REPLICATION
```
When I compare MAX on that column vs IDENT_CURRENT, I get very different result.
```
SELECT MAX(AuditTrailID) FROM dbo.tblAuditTrail
--Returns: 975799

SELECT IDENT_CURRENT('dbo.tblAuditTrail')
--Returns: 974340
```
Any idea what may have caused this?
--Vadim R.
ZZartin

SSC-Dedicated

Points: 30920
More actions
May 14, 2019 at 8:47 pm
Answer
#3642109

It sounds like someone reseeded the identity to a lower value, you should probably check who actually has access to do that in production.....

Viewing 8 posts - 1 through 7 (of 7 total)

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

It's possible someone deleted some values out of the middle of the identity then reseeded it. Or if you don't have a unique constraint on that column someone might have just reseeded the index and you potentially have duplicates in the identity column.

This reply was modified 5 years, 6 months ago by ZZartin.

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

ZZartin wrote:

It's possible someone deleted some values out of the middle of the identity then reseeded it.

SELECT quote FROM brain WHERE original = 1
0 rows returned

rVadim Hall of Fame Points: 3969 More actions · Answer 3

That identity column is a Primary Key enforced by clustered index. It is in production. Lowest value in this column is 2 (if I understand your question correctly).

DBCC CHECKIDENT returned this:

Checking identity information: current identity value '974340', current column value '975799'.

--Vadim R.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

The the identity will start to break when it catches up with where the values actually are(if it's not already breaking) although check ident should fix it.

This reply was modified 5 years, 6 months ago by ZZartin.

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

ZZartin wrote:

The the identity will start to break when it catches up with where the values actually are(if it's not already breaking) although check ident should fix it.

Yes. That's what prompted investigation in the first place. Data modifications to tables being audited started to fail. I fixed that, of course, but trying to understand what could have caused this.

--Vadim R.

rVadim Hall of Fame Points: 3969 More actions · Answer 6

I've asked around. Nobody confessed. 🙂 There is only handful of people with that level of access and I don't see why would anyone do something like that.

Thank you for your help.

--Vadim R.