Identity Value reseed did not reseed!

I would suggest TRUNCATE table rather then DELETE.

I beleive it is a flaw/limitation in DBCC CHECKIDENT.

I think this is ourr explanation...

From Books Online:

Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

I don't think that the issue has to do with delete v. truncate. I created the following test script, which can't use the truncate due to the FK constraint to mimic the OP.

I ran this, it worked and reseeded the table correctly:

SET NOCOUNT ON;
GO

CREATE TABLE Test1 
(
    ID INT IDENTITY(1,1)
    ,MyValue INT
)
GO

ALTER TABLE Test1 
    ADD CONSTRAINT PK_ID PRIMARY KEY (ID);
GO

CREATE TABLE Test2
(
    ID	INT NOT NULL
    ,SomeOtherValue INT
)

ALTER TABLE Test2 
    ADD CONSTRAINT PK_ID2 PRIMARY KEY (ID);
GO

ALTER TABLE Test2 
    ADD CONSTRAINT FK_ID FOREIGN KEY (ID)
    REFERENCES Test1 (ID);
GO


INSERT INTO Test1(MyValue) VALUES (9)
GO 1000

SELECT * FROM Test1
GO

DELETE FROM Test1
GO

DBCC CHECKIDENT(Test1, RESEED,0)
GO

INSERT INTO Test1 (MyValue) VALUES (8)
GO

SELECT * FROM Test1
GO

DROP TABLE Test2
GO
DROP TABLE Test1
GO

This was the dbcc output:

Beginning execution loop Batch execution completed 1000 times. Checking identity information: current identity value '1000', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Just a thought - Did you run those statements in the correct database?

You may have deleted from database 'Database', but the reseed would be run in the current database - which may be different!

Please try with this code.

Run it step by step. You neet to disble identity column for a moment.

-- Create test table
CREATE TABLE Test
(
id int identity,
obs varchar(50)
)

--Verify the identity column
INSERT INTO Test (obs) VALUES ('Probando id')
GO 
--Look at the id value
SELECT * FROM Test  ORDER BY id
GO
--Now we need to insert more values for id
INSERT INTO Test (obs) VALUES ('Se inserta del #2 al #51')
GO 50 
--Look at the id value
SELECT * FROM Test  ORDER BY id
GO
--DELETE 10 records of the table
DELETE FROM test WHERE id between 30 and 40
GO
-- Number of records 51 - 10
SELECT COUNT(*) FROM Test 
GO
-- id's 30 to 40 were deleted
SELECT * FROM Test  ORDER BY id
GO
-- verify the identity actual value
DBCC CHECKIDENT ("Test", NORESEED);
GO
-- Disable identity insert
/*******************************/
set identity_insert Test on
GO
-- INSERT id values 30 and 31
INSERT INTO Test (id,obs) VALUES (30,'Se inserta del #2 al #51')
GO
INSERT INTO Test (id,obs) VALUES (31,'Se inserta del #2 al #51')
GO
-- Enable identity value
set identity_insert Test off
GO
-- verify records
SELECT * FROM Test ORDER BY id
GO
-- verify value for the identity column
INSERT INTO Test (obs) VALUES ('Probando id #52')
GO