I suggest the following code to prevent it from happening:
(Instead of the code you posted in your question)
DELETE FROM dbo.table
WHERE object_id = OBJECT_ID('dbo.table')
AND last_value IS NOT NULL
DBCC CHECKIDENT ('dbo.table', RESEED, 0);
It should prevent the "reseeding-to-0".
(I have found no official documentation to support it...)
The problem is with the table
sys.syscolpars in a column named
that is the column which holds ALL the identity column information.
since that table has a row for each column in the database (not only identity columns),
NULL value in that field will represent a non-identity column
sys.identity_columns which uses
NULL for a new\truncated table).
sys.syscolpars uses a different method for holding the information:
it uses the
0x01000000010000000100000001 format (example of identity(1,1) newly created),
with the left 8 digits(position 1-8 after the
the next 8 digits (position 9-16 after the
the next 8 digits (position 17-24 after the
the 2nd digit from the right is a mystery to me,
and the 1st digit from the right - tells us if it is a new table(=1) or not!
back to our issue -
DBCC CHECKIDENT is run, it checks
if it is
NULL then it updates the new value of
1 in the right position.
that causes the next insert identity value to be treated as in a new table.
sys.identity_columns is independent of
sys.syscolpars because the first is updated immediatly, and the latter is only updated after a checkpoint is performed).
Since there is no way of querying
sys.syscolpars in real time (it requires DAC),
my suggestion is the condition above, maybe with the addition of
again, i cannot guarantee that any of this explanation is true, but it does work :)
If anyone has a more accurate information about the
DBCC CHECKIDENT background operation,
i would love to learn, please share!