September 19, 2012 at 12:06 am
Comments posted to this topic are about the item Failover or Restart Results in Reseed of Identity - FIX
The Fastest Methods aren't always the Quickest Methods
September 19, 2012 at 7:26 am
if table was truncated before restart then the first insert after will be 0 instead of 1. This may not be desired so could you sys.identity_columns instead to get value of @max-2
declare @max-2 int
select @max-2 = isnull(convert(int,last_value),1)
FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_ID=Object_ID('t1')
DBCC CHECKIDENT([dbo.t1],RESEED,@max)
if table data was deleted @max-2 is 0 and first insert id val will be 1
September 19, 2012 at 8:14 am
I don't understand why "holes" in the identity values should be considered a problem.
Can you provide more of the backstory as introduction to the script?
May 9, 2016 at 1:28 pm
Thanks for the script.
June 6, 2016 at 6:39 am
now thats some old topic but it seems this issue is still fresh. I have just saw at the connect post that this same behaviour of identity happens on SQL 2014.
does anyone have information if this "problem" is solved in any of recent Service Packs or updates? or even if SQL2016 has it?
I really dont want to put a startup proc in all my instances... sp_FixSeeds2012 🙁
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply