Title: Sudden ~1.72 billion gap in IDENTITY column between two ETL runs – what could cause this?

Michelle Moorman A 0 Reputation points
2026-10-27T18:07:15.5233333+00:00

Environment: Azure SQL Database Edition 5

Column: INT IDENTITY(1,1)

One of our tables has an identity column that suddenly jumped by about 1.72 billion. It happened between two of our normal ETL runs:

  • Run 1 finished its last insert with ID 73680612.
  • Run 2 did its first insert 7 minutes later with ID 1793678945.

The ETL loads this table via stored procedure using INSERT. Neither run reported errors or rollbacks.

What we’ve ruled out:

  • No processes use SET IDENTITY_INSERT
  • This occurred after hours and we have monitoring of all our desktop and laptop devices. No users are showing activity during the time that this occurred, so it was unlikely manual inserts or DBCC CHECKIDENT reseeds.
  • No failovers or restarts

We’ve never seen this before. What could cause a jump this large, and how can we confirm the root cause? We’re also concerned about how close we now are to the INT limit.

Azure SQL Database
0 comments

3 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-10-27T18:08:29.65+00:00

    A failed, canceled, or rolled-back large insert—potentially from ETL batching, parallel execution, or retry logic—is the leading explanation because Azure SQL Database consumes generated identity values even when the statement or transaction does not commit.

    IDENTITY does not guarantee consecutive values, and consumed values are never reused after a failed statement or rollback.^1^ A closely matching Microsoft Q&A case identifies a failed or rolled-back bulk/batch insert, retry, or background load as the realistic cause of a massive jump.^2^

    The successful status of both ETL runs does not rule this out. An intermediate statement could have failed and been retried, or its error could have been caught inside the stored procedure or application. With failovers and restarts already excluded, ordinary identity-cache loss is not a persuasive explanation for a 1.72-billion jump.

    Confirm the current state and capacity

    Run this with the actual schema and table names:

    SELECT
    OBJECT_SCHEMA_NAME(ic.object_id) AS schema_name,
    OBJECT_NAME(ic.object_id) AS table_name,
    ic.name AS identity_column,
    CONVERT(bigint, ic.last_value) AS last_generated,
    2147483647 - CONVERT(bigint, ic.last_value) AS values_remaining,
    CAST(100.0 * CONVERT(bigint, ic.last_value) / 2147483647
         AS decimal(6,2)) AS percent_consumed
FROM sys.identity_columns AS ic
WHERE ic.object_id = OBJECT_ID(N'dbo.YourTable');

DBCC CHECKIDENT (N'dbo.YourTable', NORESEED);

    sys.identity_columns.last_value is the last generated identity value, while DBCC CHECKIDENT(..., NORESEED) reports both the current identity and maximum stored column value without changing either.^3^^4^

    At 1,793,678,945, approximately 353,804,702 positive INT values remain. The maximum INT value is 2,147,483,647; BIGINT extends to 9,223,372,036,854,775,807.^5^

    Investigate the seven-minute interval

    1. Inspect ETL and application logs for the exact interval, including:
      • canceled commands and command timeouts;
      • deadlock or constraint errors;
      • retry counts;
      • unexpectedly large source-row estimates;
      • parallel workers or duplicate pipeline execution;
      • TRY/CATCH blocks that log or suppress an error and continue.
    2. Check Query Store, if it was already collecting data then. Filter executions to the interval and inspect statements targeting this table or its loading procedure. In sys.query_store_runtime_stats, execution type 3 means client-aborted and 4 means exception-aborted.^6^
    3. Review Azure SQL audit records, if auditing was enabled and configured to capture the relevant batches. Auditing can retain and analyze database activity in Azure Storage, Log Analytics, or Event Hubs.^7^ Be aware that auditing prioritizes database availability and might omit selected events during very high activity or network load.

    If neither application telemetry, Query Store, nor auditing retained the interval, the exact originating session might no longer be provable. Configure auditing and detailed ETL attempt/row-count logging now so another occurrence captures the caller, statement, attempt number, and transaction result.

    Address the exhaustion risk

    Schedule migration of the identity column and every referencing foreign-key column from INT to BIGINT. Microsoft documents that an identity column’s data type can be changed when the new type is compatible, and BIGINT is intended for values exceeding the INT range.^1^

    The core operation is:

    ALTER TABLE dbo.YourTable
    ALTER COLUMN Id BIGINT NOT NULL;

    Test the complete dependency-aware migration first: foreign keys, referencing columns, indexes, stored-procedure parameters, ETL mappings, and application models must also accept BIGINT.

    Do not reseed downward as an immediate fix. On a nonempty table, reseeding below existing values can later produce primary-key/unique-key violations or duplicate identities.^4^

    References

    1. CREATE TABLE (Transact-SQL) IDENTITY (Property)
    2. Identity Column jumps massively from 168712 to 9999993 - Microsoft Q&A
    3. sys.identity_columns (Transact-SQL)
    4. DBCC CHECKIDENT (Transact-SQL)
    5. int, bigint, smallint, and tinyint (Transact-SQL)
    6. sys.query_store_runtime_stats (Transact-SQL)
    7. Auditing for Azure SQL Database
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator
    2026-10-27T20:02:01.6133333+00:00

    First of all, jumps with IDENTITY columns are perfectly normal. SQL Server only updates the value on disk for around every thousand value, so if there is an unclean shutdown, you may "lose" some value. Could certainly happen in Azure if there is a failover.

    But 1.72 billion is certainly quite extreme, and not exactly normal. I'm afraid that what comes closest to mind is that someone, too smart for his/her own good, use SET IDENTITY_INSERT or DBCC CHECKIDENT. Did you check if there are any rows between the ids you list above? (Assuming that you got the ids from some other source the than the table itself.)

    I looked at the two ids in hex: 0x046446E4 and 0x6AE95E61, but there is nothing that looks like some sort of corruption that flipped a bit.

    Was this answer helpful?

    0 comments
  3. Paul Thomas 80 Reputation points
    2026-10-29T12:37:27.9333333+00:00

    Adding to Erland's answer: there's one way an explicit identity value gets in without anyone running SET IDENTITY_INSERT, and it fits your numbers exactly. That's a bulk load that keeps source identity values.

    Bulk-loading tools can insert explicit identity values themselves. Examples are .NET SqlBulkCopy with SqlBulkCopyOptions.KeepIdentity, the Keep identity option on an SSIS OLE DB destination, and similar "keep identity" settings in other copy tools. If a single row arrives with a large source ID, SQL Server makes that value the table's current identity value, so the next normal INSERT continues from it. Your first insert in run 2 was 1793678945, which is exactly what you'd see if a row was bulk-loaded with ID 1793678944 in between.

    What I reproduced (SQL Server 2022, INT IDENTITY(1,1), no SET IDENTITY_INSERT anywhere in the code)

    StepResultNormal insertsIDs 1, 2, 3Normal insertsIDs 1, 2, 3SqlBulkCopy with KeepIdentity, one source row with Id = 1793678944Row stored with ID 1793678944Delete that row (for example, a staging clean-up)No row left in the gapNext normal INSERT1793678945Same test without KeepIdentitySource ID ignored, next insert is 5, no jumpSo the stray row doesn't even need to still exist. The jump stays after the row is gone, which would also explain finding nothing between the two IDs.

    How to confirm it

    Check whether any rows are in the gap, as Erland suggested:

    
SELECT Id, *
FROM dbo.YourTable
WHERE Id > 73680612 AND Id < 1793678945
ORDER BY Id;

    Search Query Store for bulk loads into the table (Query Store is on by default in Azure SQL Database). A bulk load shows up as an insert bulk statement, and when the identity column appears in its column list, identity values were supplied explicitly:

    
SELECT qt.query_sql_text,
       MAX(rs.last_execution_time) AS last_run,
       SUM(rs.count_executions)    AS runs
FROM sys.query_store_query_text  AS qt
JOIN sys.query_store_query        AS q  ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan         AS p  ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE N'insert bulk%YourTable%'
GROUP BY qt.query_sql_text;

    In my test, the KeepIdentity load appeared as insert bulk [dbo].[Orders] ([Id] Int, [Val] ...), while a normal bulk load appeared as insert bulk [dbo].[Orders] ([Val] ...). Look for a last_run inside your 7-minute window.

    Check every process that writes to this table, not just people: Azure Data Factory or Synapse copy activities, SSIS packages, other ETL jobs and application services that use SqlBulkCopy, especially anything copying from another system where IDs could be in the billions.

    Next steps

    • Once you've found the source, turn off "keep identity" for that load, or map the source ID to a separate column.
    • With about 353 million values left before the INT limit, plan the move to BIGINT as the AI answer suggested. Note that changing the type of an identity primary key means rebuilding the table and recreating its keys, so schedule it carefully.

    Let me know what the Query Store query returns. If this explains it, please consider marking it as the accepted answer so others who hit the same jump can find it.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.