Identity Bug in Sql Server 2012

Apparently this bug has been hanging around since April 2012, but I only heard about it this morning.

IF OBJECT_ID('TestTable') IS NOT NULL
BEGIN
	DROP TABLE dbo.TestTable
END
GO

CREATE TABLE dbo.TestTable(
	ID		INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	Value	VARCHAR(1000) NOT NULL
)
GO

INSERT INTO dbo.TestTable(Value)
SELECT 'Test'
GO 101

checkpoint
--RESTART SQL INSTANCE

INSERT INTO dbo.TestTable(Value)
SELECT 'Test'
GO 101

SELECT * FROM dbo.TestTable

Now look at the IDENTITY values for the rows:

clip_image002[5]

Skips upwards by 1000.  Scary.

There is a connect item for this located here.  Please vote!  The connect item states that it is related to AlwaysOn.  This is not true.  This bug occurs on any restart of the sql service.

A work-around for this is to make sure to checkpoint the database before a restart, but obviously it’s not the best work-around as you don’t always control the failover (HA).  All that being said, if you checkpoint before a restart, you’ll not see the identity bug:

clip_image002[7]

 

Also, I’ve just installed Sql Server 2012 SP1 CTP and the bug still exists.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: