Has anyone seen this or have a link about it? (DBCC CHECKIDENT anomaly)

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  September 19, 2018 at 9:14 am

  #408472

  We have a busy table that I ran the following code against...

  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);
  WAITFOR DELAY '00:00:10';
  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);

  Here are the results and it's the strangest thing I've ever seen with DBCC CHECKIDENT...

  Checking identity information: current identity value '34279717', current column value '34279726'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Checking identity information: current identity value '34279731', current column value '34279734'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  
  Note the following the results above...
  

  You can see that both numbers increased but the current value of the IDENTITY is less that theCurrent Column Value in both cases on this very active table.  Also note that the difference betweenthe two values on each line is different.  And the best part of it all is that we're NOT getting any "duplicate key" errors on this table and doesn't seem to be causing any issues at all.  We have several high-insert-rate tables that are all doing the same thing.

  I've searched the web and can find no mention of this anomaly and nothing like it is mentioned in the documentation for DBCC CHECKIDENT.

  Since it doesn't seem to be causing any issues, I AM treating it as a reporting anomaly built into DBCC CHECKIDENT that has started to rear its ugly head due to the blazing fast SSDs but would love to see some documentation on it if anyone else has run into this.

  Thanks, folks..

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  September 19, 2018 at 10:29 am

  #2006298

  Jeff Moden - Wednesday, September 19, 2018 9:14 AM

  We have a busy table that I ran the following code against...

  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);
  WAITFOR DELAY '00:00:10';
  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);

  Here are the results and it's the strangest thing I've ever seen with DBCC CHECKIDENT...

  Checking identity information: current identity value '34279717', current column value '34279726'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Checking identity information: current identity value '34279731', current column value '34279734'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  
  Note the following the results above...
  

  You can see that both numbers increased but the current value of the IDENTITY is less that the Current Column Value in both cases on this very active table.  Also note that the difference between the two values on each line is different.  And the best part of it all is that we're NOT getting any "duplicate key" errors on this table and doesn't seem to be causing any issues at all.  We have several high-insert-rate tables that are all doing the same thing.

  I've searched the web and can find no mention of this anomaly and nothing like it is mentioned in the documentation for DBCC CHECKIDENT.

  Since it doesn't seem to be causing any issues, I AM treating it as a reporting anomaly built into DBCC CHECKIDENT that has started to rear its ugly head due to the blazing fast SSDs but would love to see some documentation on it if anyone else has run into this.

  Thanks, folks..

  My thought is that DBCC CHECKIDENT is doing an allocation ordered scan (nolock type) and the difference are the uncommitted increments.
  😎 

  And of course, you should do as the message says, contact your system administrator 😀

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  September 19, 2018 at 11:08 am

  #2006310

  Eirikur Eiriksson - Wednesday, September 19, 2018 10:29 AM

  Jeff Moden - Wednesday, September 19, 2018 9:14 AM

  We have a busy table that I ran the following code against...

  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);
  WAITFOR DELAY '00:00:10';
  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);

  Here are the results and it's the strangest thing I've ever seen with DBCC CHECKIDENT...

  Checking identity information: current identity value '34279717', current column value '34279726'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Checking identity information: current identity value '34279731', current column value '34279734'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  
  Note the following the results above...
  

  You can see that both numbers increased but the current value of the IDENTITY is less that the Current Column Value in both cases on this very active table.  Also note that the difference between the two values on each line is different.  And the best part of it all is that we're NOT getting any "duplicate key" errors on this table and doesn't seem to be causing any issues at all.  We have several high-insert-rate tables that are all doing the same thing.

  I've searched the web and can find no mention of this anomaly and nothing like it is mentioned in the documentation for DBCC CHECKIDENT.

  Since it doesn't seem to be causing any issues, I AM treating it as a reporting anomaly built into DBCC CHECKIDENT that has started to rear its ugly head due to the blazing fast SSDs but would love to see some documentation on it if anyone else has run into this.

  Thanks, folks..

  My thought is that DBCC CHECKIDENT is doing an allocation ordered scan (nolock type) and the difference are the uncommitted increments.
  😎 

  And of course, you should do as the message says, contact your system administrator 😀

  Yep... agreed.  That's all I can come up with myself on this.  I'm totally gob smacked that such a thing could occur because no one, especially MS, would ever use such a thing to cause such a problem, right?  😛:Whistling::sick:

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  September 19, 2018 at 11:14 am

  #2006311

  Just did a quick test and I'm certain that current identity value is the committed value and the current column value is the uncommitted.
  😎 

  USE TEEST;
GO
SET NOCOUNT ON;

  CREATE TABLE dbo.TEST_DBCC_CHECKIDENT
(
  TDC_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
 ,TDC_DT DATETIME NOT NULL
);

  DECLARE @ITERCOUNT INT = 100000000;
DECLARE @ITER   INT = 0;

  WHILE @ITER < @ITERCOUNT
BEGIN
  INSERT INTO dbo.TEST_DBCC_CHECKIDENT (TDC_DT) VALUES(GETDATE());
  SET @ITER = @ITER + 1;
END

  And in another session

  
USE TEEST;
GO
SET NOCOUNT ON;
DBCC CHECKIDENT ('dbo.TEST_DBCC_CHECKIDENT',NORESEED);
WAITFOR DELAY '00:00:10';
DBCC CHECKIDENT ('dbo.TEST_DBCC_CHECKIDENT',NORESEED);


  Results
  
Checking identity information: current identity value '37749', current column value '37749'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '123561', current column value '123563'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '334037', current column value '334046'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '417048', current column value '417057'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  September 19, 2018 at 11:20 am

  #2006312

  Jeff Moden - Wednesday, September 19, 2018 11:08 AM

  Eirikur Eiriksson - Wednesday, September 19, 2018 10:29 AM

  Jeff Moden - Wednesday, September 19, 2018 9:14 AM

  We have a busy table that I ran the following code against...

  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);
  WAITFOR DELAY '00:00:10';
  DBCC CHECKIDENT ('dbo.DocumentAttachment',NORESEED);

  Here are the results and it's the strangest thing I've ever seen with DBCC CHECKIDENT...

  Checking identity information: current identity value '34279717', current column value '34279726'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Checking identity information: current identity value '34279731', current column value '34279734'.
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  
  Note the following the results above...
  

  You can see that both numbers increased but the current value of the IDENTITY is less that the Current Column Value in both cases on this very active table.  Also note that the difference between the two values on each line is different.  And the best part of it all is that we're NOT getting any "duplicate key" errors on this table and doesn't seem to be causing any issues at all.  We have several high-insert-rate tables that are all doing the same thing.

  I've searched the web and can find no mention of this anomaly and nothing like it is mentioned in the documentation for DBCC CHECKIDENT.

  Since it doesn't seem to be causing any issues, I AM treating it as a reporting anomaly built into DBCC CHECKIDENT that has started to rear its ugly head due to the blazing fast SSDs but would love to see some documentation on it if anyone else has run into this.

  Thanks, folks..

  My thought is that DBCC CHECKIDENT is doing an allocation ordered scan (nolock type) and the difference are the uncommitted increments.
  😎 

  And of course, you should do as the message says, contact your system administrator 😀

  Yep... agreed.  That's all I can come up with myself on this.  I'm totally gob smacked that such a thing could occur because no one, especially MS, would ever use such a thing to cause such a problem, right?  😛:Whistling::sick:

  We need more information on the identity mechanism to understand what is happening, although we know more or less what it is. Hence, one cannot run DBCC CHECKIDENT against a @Table variable as it implies "read uncommitted"
  😎

  Maybe Paul R could enlighten us here?