SQL Server Performance

What can reset and IDENTITY column besides TRUNCATE and DBCC CHECKIDENT?

Discussion in 'SQL Server 2005 General DBA Questions' started by hominamad, Apr 15, 2008.

  1. hominamad Member

    Hello all,
    I have a situation where we have a table that 99% of the time is empty. It's an outgoing message queue table. Once messages are picked up, they are moved to an archive table. There is an identity column on this table which is crucial to maintaining the order of the messages. I noticed that occasionaly when I insert a record into the table, after not having tested it for a few days, the identity has reset back to 1 again. As far as I know, nobody is truncating this table or issuing any reseed commands. What else could possibly be responsbile for reseeding this column?
    Are there any maintenance commands which when the table is empty maybe reseeds it? This is really a mystery for me.
  2. jagblue New Member

    also if you drop and re create table resat your identiy
  3. hominamad Member

    I want to bump this post up as it seems to still be happening. Does anyone know of any background SQL jobs or proccesses that could reset an identity seed for an empty table?
  4. SmartDBA New Member

    No, there is no internal process run by SQL Server to reset the identity seed value to 0 for an empty table.
    The table seed vale gets reseted to zero only when the table is truncated or some one has executed DBCC CHECKIDENT ('TableName', reseed, 0). Check your application code to identify is there a code written which runs DBCC CHECKIDENT once the message is picked up and moved to the archive table.
  5. FrankKalis Moderator

    What about running a trace for a couple of days against this table to find the culprit. To the best of my knowledge, SQL Server doesn't decide on its own to reset such a value. [:)]
  6. Madhivanan Moderator

    My guess is that the table is being dropped and created often

Share This Page