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.
May 14, 2019 at 6:51 pm
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.
May 14, 2019 at 7:15 pm
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.
May 14, 2019 at 8:25 pm
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.
It sounds like someone reseeded the identity to a lower value, you should probably check who actually has access to do that in production.....
May 15, 2019 at 6:01 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply