April 5, 2016 at 4:24 am
Dear Team,
I have a Table "TokenHdr" in which combination of "LocId" & "RowId" is forming a PK.
"RowId" is a [bigint] IDENTITY column increment by 1.
"LocId" holds value of LocationID i.e. a Location from where local DB "QMS" gets accessed by branch users.
"TokenHdr" is a table in local DB "QMS" which holds transaction records of Token Generations.
Approx. 1500 Tokens gets generated and inserted into "TokenHdr" table daily through a Desktop (.Net) Application.
The local Database is on Windows 7 32-bit OS System.
Recently, the new HDD containing Windows 8 64-bit OS and new blank (fresh) database "QMS" is replaced on Database Machine. The old "QMS" database is restored as "QMS_Old".
Now the task was to insert data from "QMS_Old.dbo.TokenHdr" to new "QMS.dbo.TokenHdr".
Table "QMS_Old.dbo.TokenHdr" contains 10000 records (Identity Value i.e. RowId from 233 - 10233)
After inserting 10000 records into new "QMS.dbo.TokenHdr", Identity Value i.e. RowId became 1 - 10000.
Now, using DBCC CHECKIDENT(TokenHdr, RESEED, 10233) through another utility Application, I reset IDENTITY -- one time Activity only.
Through Desktop Application, new Tokens got generated starting from 10234 ..... as expected.
Few days, let's say 5 days, it worked properly as expected.
Suddenly on 6th day, (without executing DBCC command or utility application) IDENTITY value got automatically reseed to 10233 causing VIOLATION OF PRIMARY KEY Error.
After resetting IDENTITY values, Application & Database worked again properly for next few days.
Again the same issue got repeated.
IDENTITY value got automatically reseed to some lower random value (without executing DBCC command or utility application) causing VIOLATION OF PRIMARY KEY Error.
What could be the cause of this IDENTITY behavior of jumping to some lower value ?
What should be the solution ?
Dear Team.... please guide me ... as it majorly impacting on Client's huge data.
April 5, 2016 at 4:30 am
Are you sure the identity was reseeded and that somebody (or something) didn't try an identity insert? Has anybody run TRUNCATE TABLE - that would reseed the identity?
John
April 5, 2016 at 5:32 am
Hi John,
DBCC command or utility Application gets executed only once after data sync from old database to new database.
It is a one time activity unless and until new HDD with blank fresh Database gets replaced on Database Machine at Client's place.
One thing is to mention .... the load on Database Machine is little high.
Some times due to heavy load, SQL service and Agent gets auto stopped causing some Jobs execution in-active.
SQL service gets started only after manual intervene.
Is this Stop-Start of SQL Service is causing IDENTITY value to jump to lower random value ?
(As per my knowledge, in such scenario, IDENTITY value jumps to upper random value .... plz correct me if I am worng)
April 5, 2016 at 5:51 am
Kishore M. K. (4/5/2016)
Is this Stop-Start of SQL Service is causing IDENTITY value to jump to lower random value ?
No, I don't think so, but it's definitely something you should look into. You don't want your live server constantly stopping unexpectedly.
I'm not sure what's going on with your identity reseeds. If I were you, I'd run a server-side trace or extended events session in order to capture DBCC CHECKIDENT or TRUNCATE statements. Then, next time you get a primary key violation that looks as if it was caused by this issue, check your trace to see whether there were any recent DBCCs or CHECKIDENTs.
John
April 5, 2016 at 6:01 am
Thank you very much John for your reply.
Steps mentioned by you will surely helpful for me to trace the issue.
April 5, 2016 at 7:16 am
Kishore M. K. (4/5/2016)
Is this Stop-Start of SQL Service is causing IDENTITY value to jump to lower random value ?
This has been a known issue on atleast SQL Serve 2012 where identity value jumps by 1000 values in case of SQL restart and there is MS connect item which was closed stating it as a feature and not a bug.
https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-fail
Not sure if its relevant to your case as you are using 2008 or 2008 R2.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
April 5, 2016 at 10:19 am
Sachin Nandanwar (4/5/2016)
Kishore M. K. (4/5/2016)
Is this Stop-Start of SQL Service is causing IDENTITY value to jump to lower random value ?
This has been a known issue on atleast SQL Serve 2012 where identity value jumps by 1000 values in case of SQL restart and there is MS connect item which was closed stating it as a feature and not a bug.
https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-fail
Not sure if its relevant to your case as you are using 2008 or 2008 R2.
And the problem is that the identity is reseeded downwards, not upwards by 1000. This has to do primarily with how SEQUENCE is implemented so probably has no bearing on a 2008 server. I suspect it's probably a RESEED statement somewhere in the data load process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2016 at 2:04 pm
No, a stop / start of SQL Server will not cause an identity value to go down. Your code must be resetting it for that to happen.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply