When working with SQL Server tables that use IDENTITY columns, it's alarming to discover sudden massive jumps in the seed values. In one particular case I investigated, a table with just 20,000 rows showed an IDENTITY value exceeding 560 million, despite having an increment value of 1. This clearly indicates some unusual activity is occurring.

The most effective way to investigate this is by using SQL Server Profiler to monitor activity specifically on the problematic table. Here's how to configure it:

-- First, identify your table's object ID
SELECT OBJECT_ID('YourSchema.YourTable') AS TableObjectID;

-- Sample Profiler trace setup
USE master;
GO

-- Create a trace
DECLARE @TraceID INT;
DECLARE @MaxFileSize BIGINT = 50; -- MB
EXEC sp_trace_create @TraceID OUTPUT, 0, N'C:\Traces\IdentityJumpTrace', @MaxFileSize, NULL;

-- Set the events you want to capture
EXEC sp_trace_setevent @TraceID, 42, 1, 1; -- SQL:StmtStarting
EXEC sp_trace_setevent @TraceID, 43, 1, 1; -- SQL:StmtCompleted
EXEC sp_trace_setevent @TraceID, 47, 1, 1; -- Object:Created
EXEC sp_trace_setevent @TraceID, 93, 1, 1; -- UserConfigurable:0

-- Add filters to focus on your specific table
DECLARE @TableObjectID INT = OBJECT_ID('YourSchema.YourTable');
EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'%YourTable%');
EXEC sp_trace_setfilter @TraceID, 35, 0, 0, @TableObjectID);

-- Start the trace
EXEC sp_trace_setstatus @TraceID, 1;

When investigating identity jumps, pay special attention to these events in your trace:

• SQL:BatchCompleted - Shows complete transactions that might include reseeding
• SQL:StmtStarting - Captures individual statements that modify the table
• Audit Schema Object Access Event - Tracks direct table modifications
• Object:Altered - In case someone modifies the table structure

Through profiling, I've identified several scenarios that can cause these jumps:

-- 1. Manual reseeding (DBCC CHECKIDENT)
DBCC CHECKIDENT ('YourTable', RESEED, 500000000);

-- 2. Bulk inserts with explicit values
INSERT INTO YourTable (ID, ...)
SELECT 500000000 + ROW_NUMBER() OVER (ORDER BY ...), ...
FROM SomeLargeSource;

-- 3. IDENTITY_INSERT being enabled
SET IDENTITY_INSERT YourTable ON;
INSERT INTO YourTable (ID, ...) VALUES (500000001, ...);
SET IDENTITY_INSERT YourTable OFF;

When examining the trace output, look for these patterns:

1. Transactions containing DBCC CHECKIDENT commands
2. Large batch inserts with explicit ID values
3. Any operations that enable IDENTITY_INSERT
4. Unusual login times that might correlate with the jumps

For a more lightweight solution, consider using Extended Events:

CREATE EVENT SESSION [IdentityMonitor] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],'%YourTable%'))
),
ADD EVENT sqlserver.object_altered
(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([object_name],'%YourTable%'))
)
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\IdentityMonitor.xel');
GO

ALTER EVENT SESSION [IdentityMonitor] ON SERVER STATE = START;
GO

When working with an identity column in SQL Server, you expect sequential increments based on your seed settings. However, encountering massive jumps (like from 20,000 rows to 560,000,000) indicates something unusual is happening with your IDENTITY column. Let's explore how to diagnose this using SQL Server Profiler.

First, launch SQL Server Profiler from SQL Server Management Studio. Create a new trace with these specific settings:

-- Sample T-SQL to create a server-side trace (alternative to Profiler GUI)
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT = 50
DECLARE @FilePath NVARCHAR(256) = N'C:\Traces\IdentityJumpTrace'

EXEC sp_trace_create 
    @TraceID OUTPUT,
    0, 
    @FilePath, 
    @maxfilesize, 
    NULL

The key is adding precise filters to capture only relevant events:

1. In the Events Selection tab:
   • Select "Stored Procedures" → "SP:StmtCompleted"
   • Select "TSQL" → "SQL:BatchCompleted"
2. In the Column Filters:
   • Set "ObjectName" to your table name
   • Add "TextData" LIKE '%IDENTITY_INSERT%'

These events often reveal identity value manipulation:

-- Example of what might appear in traces
SET IDENTITY_INSERT YourTable ON;
INSERT INTO YourTable (IDColumn, ...) VALUES (560000000, ...);
SET IDENTITY_INSERT YourTable OFF;

-- Or through DBCC commands
DBCC CHECKIDENT ('YourTable', RESEED, 560000000);

While waiting for Profiler data, run these queries to gather evidence:

-- Check current identity value
DBCC CHECKIDENT ('YourTable', NORESEED);

-- Find recent identity insert operations
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    last_value AS LastIdentityValue,
    created AS SeedCreatedTime
FROM sys.identity_columns
WHERE object_id = OBJECT_ID('YourTable');

-- Check for manual reseeds in default trace
SELECT 
    StartTime,
    TextData,
    HostName,
    LoginName
FROM sys.fn_trace_gettable(
    CONVERT(VARCHAR(150), 
        (SELECT TOP 1 value FROM sys.fn_trace_getinfo(NULL) WHERE property = 2)
    ), DEFAULT)
WHERE TextData LIKE '%CHECKIDENT%' 
   OR TextData LIKE '%IDENTITY_INSERT%';

Once you identify the cause, implement these safeguards:

-- Create audit trigger
CREATE TRIGGER tr_AuditIdentityChanges
ON YourTable
AFTER INSERT
AS
BEGIN
    DECLARE @MaxID INT, @RowCount INT;
    
    SELECT @RowCount = COUNT(*) FROM inserted;
    SELECT @MaxID = MAX(IDColumn) FROM YourTable;
    
    IF @MaxID > (IDENT_CURRENT('YourTable') + @RowCount * 1000)
    BEGIN
        INSERT INTO IdentityAuditLog
        SELECT 
            SUSER_SNAME(),
            GETDATE(),
            IDENT_CURRENT('YourTable'),
            @MaxID,
            @RowCount
    END
END;

For SQL Server 2012+, consider this more performant alternative:

-- Create extended events session
CREATE EVENT SESSION [IdentityValueChanges] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    WHERE ([sqlserver].[like_i_sql_unicode_string].[object_name]=N'YourTable'))
ADD TARGET package0.event_file(SET filename=N'IdentityValueChanges');
GO

ALTER EVENT SESSION [IdentityValueChanges] ON SERVER STATE = START;