IDENT_CURRENT is out of sync

  • rVadim

    Hall of Fame

    Points: 3897

    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: 30338

    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 3 months, 1 week ago by  ZZartin.
  • Y.B.

    SSChampion

    Points: 11361

    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: 3897

    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: 30338

    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 3 months, 1 week ago by  ZZartin.
  • rVadim

    Hall of Fame

    Points: 3897

    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.

  • ZZartin

    SSC-Dedicated

    Points: 30338

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

  • rVadim

    Hall of Fame

    Points: 3897

    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 8 (of 8 total)

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