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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply