June 19, 2012 at 12:57 pm
Hello,
Create a table with an Identity column, insert data / restart the server / insert more data / restart the server/ insert some more data.
My data looks like this :
Identity column
1
2
3
1002
1003
1004
1005
2002
2004
It looks like the indentity value gain +~1000 after most server restart (sometimes identity stay the same). This can be very dangerous for some datatype!
The only thing google told me was this : https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity]
..but microsoft did not comment on it yet!
Is there a fix or work-around for this ?
June 19, 2012 at 12:58 pm
do you have always on in play here?
what is exact table definition?
are you doing any rollbacks?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 19, 2012 at 1:00 pm
Yes, it's a known issue as per that connect item.
Workaround - don't restart SQL 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 1:06 pm
I dont have always on ( i am on standard version)
For the table definition. It does this with any table with an identy field on it. I found this behavior when i realised that all the table in my database that had an identity field were increased by 1000.
I made some test with this one :
CREATE TABLE dbo.TestTable
(
TestID int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
ALTER TABLE dbo.TestTable ADD CONSTRAINT
PK_TestTable PRIMARY KEY CLUSTERED
(
TestID
) ON [PRIMARY]
GO
I do not think i have some rollback since i only do simple insert and then i restart the server.
June 19, 2012 at 1:06 pm
By the way... Identity has never guaranteed no gaps. This is a bit extreme, but there's nothing technically invalid about it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 1:09 pm
hi2u (6/19/2012)
I dont have always on ( i am on standard version)
It has nothing to do with AlwaysOn. It's just the restart of SQL that causes it.
Workaround for the moment (until MS gets around to fixing the problem) is not to restart SQL frequently (which is a general good practice anyway for production servers)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 1:09 pm
hi2u (6/19/2012)
Hello,Create a table with an Identity column, insert data / restart the server / insert more data / restart the server/ insert some more data.
My data looks like this :
Identity column
1
2
3
1002
1003
1004
1005
2002
2004
It looks like the indentity value gain +~1000 after most server restart (sometimes identity stay the same). This can be very dangerous for some datatype!
The only thing google told me was this :
..but microsoft did not comment on it yet!
Is there a fix or work-around for this ?
June 19, 2012 at 1:15 pm
I guess the insert hyperlink button is broken 😉
And thank you Gila, thats exactly the answer i was looking for (if a fix already exists or not for this !)
June 19, 2012 at 1:58 pm
I wasn't aware that this bug hits on a restart of a non-AlwaysOn sql server! I wonder what happens if the ~1000 bump up blows you past a datatype limit...
In any case, I did a quick review of CU1 and CU2 for SQL 2012 and didn't find any mention of identity. But WOW are there some nasty bugs that did get fixed ... sheesh!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2012 at 7:56 am
If the value of the PK is vital, and gaps cannot be supported, I'd look at implementing one of 2012's new features -- a SEQUENCE. You can programmatically control the value that way. I have no ability to comment on its performance, though.
June 20, 2012 at 9:19 am
jeff.mason (6/20/2012)
If the value of the PK is vital, and gaps cannot be supported, I'd look at implementing one of 2012's new features -- a SEQUENCE.
Sequences and identities use the same code behind the scenes, a sequence does not guarantee no gaps (and in fact, I suspect this gap 'bug' is due to changing identities to work as sequences do, but that's purely a guess on my part)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2012 at 11:56 am
Really? Well, then, that is kind of a negative about sequences then, I would think. I would have thought they would have been more static/controllable. Obviously I don't have hands on with them yet.
June 20, 2012 at 3:19 pm
I've done a bit of testing, and it doesn't increase by 1000, it increases to the next value greater than the last identity value using this formula: New Identity = (N*INCREMENT*1000)+Seed .
So for (1,1) if the identity value was 3 before the restart it is increased to 1001, and the next inserted value will be 1002. At 4999 before the restart it is increased to 5001, and the next inserted value will be 5002.
For (5,10), pre-restart values of 5,15,20,25 jumps to 10005, then 20005 etc.
I've checked a few pairs and it seems consistent.
It only happens if you have inserted a row in the table since the last restart, so two successive restarts don't cause a double jump.
However you can create your own identity generating process, but you need one for each identity column you have with this bit of code. I can't remember where I got the original, but it's big advantage is you don't get deadlocking on the table.
CREATE TABLE [dbo].[Seeder](
[Seed_Col] [int] NOT NULL
) ON [PRIMARY]
go
insert into Seeder values(1)
go
create trigger Tr_Seed on Seeder
for update
as
select Seed_Col from deleted
-- every update below returns the previous value and causes an increment
update seeder set Seed_Col = Seed_Col + 1
You could also create an "on startup" procedure that selects the MAX identity column value and then does DBCC CHECKIDENT (TableName, RESEED, @max-2 )
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 20, 2012 at 4:26 pm
My "on startup" procedure is a viable workaround here is the code as I tested it. Note that you need to enable the "scan for startup procs" configuration which requires a service restart, and create the stored proc in Master.
create proc FixIdent_IdentityTest
as
begin
declare @max-2 int
select @max-2 = max(ID_Col) from Scratch.dbo.IdentityTest
DBCC CHECKIDENT([Scratch.dbo.IdentityTest],RESEED,@MAX)
end
sp_procoption @ProcName = 'FixIdent_IdentityTest'
, @OptionName = 'startup'
, @OptionValue = 'true'
Most DBAs don't like using startup procs, but this is a short term solution.
Cheers
Leo
Nothing in SQL Server is ever so complicated that with a bit of work can't be made more complicated!
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 20, 2012 at 4:38 pm
GilaMonster (6/20/2012)
jeff.mason (6/20/2012)
If the value of the PK is vital, and gaps cannot be supported, I'd look at implementing one of 2012's new features -- a SEQUENCE.Sequences and identities use the same code behind the scenes, a sequence does not guarantee no gaps (and in fact, I suspect this gap 'bug' is due to changing identities to work as sequences do, but that's purely a guess on my part)
This article spesifically mentiones the 1000 jump "bug" in the SEQUENCE functionality.
http://technet.microsoft.com/en-us/magazine/hh407114.aspx
Here is the extract:
Unlike the IDENTITY property, a sequence only writes to the database’s metadata that the values were issued when they are first put into the cache. For example, if a sequence issued rows in batches of 1,000, when the first batch is issued the value of 1,000 is written to the metadata. When the value of 1,001 is needed, another 1,000 values are loaded into the cache and the value of 2,000 is written to the metadata. This greatly reduces the number of metadata writes and can improve database performance.
The one downside to this is that there will be gaps in the values issued by the sequence every time SQL Server is restarted. When the database shuts down, it doesn’t write the last value actually used to metadata. So if the last value used before shutdown was 1,005 (and we have the same settings as previously described), when the next row is inserted after the database comes back online, the next value used will be 2,000.
Leo
Nothing in SQL Server is ever so complicated that with a bit of work can't be made more complicated!
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply