Identity column problem

Question

Identity column problem

Noel Kennedy

Ten Centuries

Points: 1214
More actions
November 11, 2009 at 7:02 am

#217462

I have noticed an extremely odd behavior that has just started with identity columns across more than one database on a server.
What happens is that the values generated are no longer increasing in the way I have come to expect, ie max(last value) + 1. Instead, seemingly random values are picked, some below the max value, some above. This is occurring in more than one table, and in more than one database on the server.
To provide more info about the sequence of events:
Two databases were just restored from backup taken on another server. Initial values are the values that are recorded as soon as the DB is restored.
I have a table whose initial max ID value was 4133. The first insert produced 4126, second 4134, third 4162, forth 4172, fifth 4173, 6th 4174.
I have another table whose sequence goes like this: initial max ID 5522. 1st insert 5516, second 5589, third 5591, forth 5590.
When I run:
DBCC CHECKIDENT ('my table', NORESEED)
declare @maxValue int
SELECT @maxValue = max( [my pk column which is identity])
FROM [dbo].[my table]
print 'actual max value = '
print @maxvalue
I get:
Checking identity information: current identity value '4171', current column value '4171'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
actual max value =
4162
From technet I understand that the current column value should be 'the current maximum value of the identity column'.
I don't understand how the two can be out of synch!!
FYI, DBCC CHECKDB returns no errors
Anyone experienced this before or any ideas on how to fix / diagnose?
Thanks,
🙂
Background:
Windows 2003 server
SQL Server 2005 SP3 (9.0.4035)
Developer edition

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

You could have had rows deleted or a rollback occur. The identity column will increment even if a rollback occurs. See the below script for an example.

CREATE TABLE #temp (id INT IDENTITY(1,1), test VARCHAR(10))

DECLARE @maxid int

INSERT INTO #temp

SELECT 'test1'

UNION ALL

SELECT 'test2'

DBCC CHECKIDENT('#temp',NORESEED)

SELECT @maxid = MAX(id) FROM #temp

PRINT @maxid

BEGIN TRANSACTION

INSERT INTO #temp

SELECT 'test3'

UNION ALL

SELECT 'test4'

ROLLBACK TRANSACTION

DBCC CHECKIDENT('#temp',NORESEED)

SELECT @maxid = MAX(id) FROM #temp

PRINT @maxid

DROP TABLE #temp

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 2

Sorry, I just updated my post, I think we were posting at the same time!

As far as I am aware, rollbacks would not produce the behavior I am seeming:

I have a table whose initial max ID value was 4133. The first insert produced 4126, second 4134, third 4162, forth 4172, fifth 4173, 6th 4174.

I maybe wrong, but I thought that rollbacks would only produce an identity sequence with gaps, not out of order?

matt6288 SSCertifiable Points: 5058 More actions · Answer 3

Yeah that is odd. Have you checked the identity seed and increment? I am not sure what else would be causing that.

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 4

Matt Wilhoite (11/11/2009)
Yeah that is odd. Have you checked the identity seed and increment? I am not sure what else would be causing that.

Seed and increment are 1 and 1.

I have just restored the same backup to a different DB on the same server. It is not exhibiting the same problem. And the earlier restored database is behaving normally again!! Weird, I'm just glad this is not the production server!!