SQL Server – Sudden PRIMARY KEY Violation on IDENTITY Column

identityprimary-keysql serversql-server-2012

I have a log table with an ID identity column. Everything works fine for years; then, yesterday, I see the following error in the logs:

The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK__log__ID__3B40CD36'. Cannot insert duplicate key in object 'dbo.log'. The duplicate key value is (295992).

INSERT INTO log (datum, zeit, benutzer, modul, prozedur, code, zeile, bez1, bez2, tech_info) VALUES ('20151126 00:00:00.000', '19000101 18:26:45.121', 'Customer', '', '', 'WShop-Trans', 0, '1 Datensätze für Tabelle adresse gesendet.', '', '')

I checked the IDENTITY seed, and it looks OK:

Query: DBCC CHECKIDENT(log)

Result: Checking identity information: current identity value '296021', current column value '296021'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Query: SELECT MAX(ID) FROM log

Result: 296021

There are no triggers on the table, and nobody is messing around with the seed values (I'm the one administering the database server, so I'm pretty sure about that).

So far, this is a one-time event, and I cannot reproduce it.

I looks like just an SQL Server glitch, but I'm curious: Is this a known bug, or is there any other plausible explanation for this? The SQL Server version is Microsoft SQL Server 2012 - 11.0.2100.60 (X64).

For completeness, here's the complete table script:

CREATE TABLE [log](
    [datum] [datetime] NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [zeit] [datetime] NULL,
    [benutzer] [varchar](255) NULL,
    [modul] [varchar](255) NULL,
    [prozedur] [varchar](255) NULL,
    [code] [varchar](255) NULL,
    [zeile] [int] NULL,
    [bez1] [text] NULL,
    [bez2] [text] NULL,
    [tech_info] [text] NULL,
    [pc_name] [varchar](255) NULL,
    [app_name] [varchar](255) NULL,
    [s_insert_user] [nvarchar](255) NULL,
    [s_insert_dat] [datetime] NULL,
    [s_update_user] [nvarchar](255) NULL,
    [s_update_dat] [datetime] NULL,
    [fs_mandant] [uniqueidentifier] NULL,
 CONSTRAINT [PK__log__ID__3B40CD36] PRIMARY KEY CLUSTERED ([ID] ASC)
)

CREATE NONCLUSTERED INDEX [code] ON [log] ([code] ASC)
CREATE NONCLUSTERED INDEX [datum_zeit] ON [log] ([datum] ASC, [zeit] ASC)
CREATE NONCLUSTERED INDEX [fs_mandant] ON [log] ([fs_mandant] ASC)
CREATE NONCLUSTERED INDEX [modul] ON [log] ([modul] ASC)

Best Answer

Since the question states SQL Server 2012 RTM (build 2100) is in use, it is likely this bug:

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure

which says:

Assume that you create a sequence object that has the CACHE option enabled in Microsoft SQL Server 2012 or SQL Server 2014. When the instance is under memory pressure, and multiple concurrent connections request sequence values from the same sequence object, duplicate sequence values may be generated. In addition, a unique or primary key (PK) violation error occurs when the duplicate sequence value is inserted into a table.

Note that IDENTITY uses the sequence object mechanism in SQL Server 2012 and later.

The issue was first fixed in:

  • Cumulative Update 6 for SQL Server 2014
  • Cumulative Update 4 for SQL Server 2012 SP2
Related Question