March 31, 2014 at 7:25 pm
Hi,
I have a table with PK column defined like IDENTITY(1,1), it has 16 entries, now after series of migration and restores I'm adding new row into it like below and get new TypeID = 1001 ? Totaly lost is this somewhere go reset to 1000? Before was only 16 entries with correct sequence 1 by 1 thru 16.
What could be wrong , can anybody help.
Best M
CREATE TABLE [dbo.].[Types](
[TypeID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Code] [nvarchar](350) NULL,
[Description] [nvarchar](350) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [pkTypes] PRIMARY KEY CLUSTERED
([TypeID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )
INSERT dbo.Types
( Code ,
Description ,
IsActive
)
VALUES ( N'Alpha' , -- Code - nvarchar(50)
N'Alpha Type 17' , -- Description - nvarchar(250)
1 -- IsActive - bit )
select * from dbo.Types
March 31, 2014 at 8:27 pm
If you shut down SQL Server there is a known bug that advances the identity values. I don't know if there is a fix for that or not.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2014 at 8:29 pm
Here is a link with more information:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2014 at 9:14 pm
Also remember that if you begin a transaction and rollback, the identity values will also have increased afterwards. That makes sense if you think about it.
Identity is a count of attempted inserts, not consecutive sequential numbers. AFAIK you can't even really trust a sequence data type for that purpose either.
You can do a DBCC CHECKIDENT(blah, RESEED) to reset it to the next available number though.
March 31, 2014 at 10:35 pm
Thanks, all
Yes, I did reseed and it worked.
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
April 1, 2014 at 2:09 am
Bear in mind that identity has never been guaranteed to not have gaps. It's an ever-increasing number, that's all. It's not gap-free and it's not even guaranteed unique (you can reseed under the current max value)
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
April 1, 2014 at 3:45 am
TheSQLGuru (3/31/2014)
If you shut down SQL Server there is a known bug that advances the identity values. I don't know if there is a fix for that or not.
according to MS it's not a bug, it's by design.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 1, 2014 at 3:47 am
mario17 (3/31/2014)
Thanks, allYes, I did reseed and it worked.
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
The problem here is, when the value jumped if identities were created in the upper range and you reseed back to 10, you'll have issues later
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 2, 2014 at 1:09 am
Also note that with Replicated Tables you can/will also have gaps, as each subscriber of the published Table is pre-allocated a range of IDs it can use.
December 19, 2014 at 12:23 pm
Gaps I don't mind, but really 1000 or 10,000 at a time? It's too much. I guess if you only reboot once a year then it won't matter much, but any more frequently than once a month, and you won't be able to read my invoice or order numbers any more.
December 20, 2014 at 3:25 am
Does anyone know if it is still an issue with SQL Server 2014?
It looks like MS was putting up some kind of claim that it was by design. Meaning "We really screwed up and have no easy way to fix it, so we are just going to pretend that this is what we wanted."
December 20, 2014 at 7:51 am
Yes, it is in SQL Server 2014. And it IS by design, and for a good reason. The logging of each individual identity created is a SIGNIFICANT performance bottleneck. The new design only needs to log the gapped value. The problem is that a checkpoint is supposed to be performed when the engine shuts down gracefully, but it doesn't do that when you use the config tool or windows services mmc to shut down the engine (nor on AG failover). You need to issue the SHUTDOWN command (WITHOUT the NO WAIT flag) to avoid the gap issue.
There is also a trace flag you can set to get the older behavior back: TF 272.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 22, 2014 at 8:19 pm
Dan Guzman - Not the MVP (12/19/2014)
Gaps I don't mind, but really 1000 or 10,000 at a time? It's too much. I guess if you only reboot once a year then it won't matter much
The 1000/10K/whatever is (IIRC) allocated once PER SUBSCRIPTION, so reboots (of either server or subscribed client) are irrelevant. Once the allocation is half-used, it grabs some more, so you only need to allocate enough to keep it happy between synchs.
December 22, 2014 at 8:21 pm
brewmanz (12/22/2014)
Dan Guzman - Not the MVP (12/19/2014)
Gaps I don't mind, but really 1000 or 10,000 at a time? It's too much. I guess if you only reboot once a year then it won't matter muchThe 1000/10K/whatever is (IIRC) allocated once PER SUBSCRIPTION, so reboots (of either server or subscribed client) are irrelevant. Once the allocation is half-used, it grabs some more, so you only need to allocate enough to keep it happy between synchs.
I don't understand your use of the term "SUBSCRIPTION" here. It seems unassociated with the identity increment issue.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply