Please look at the below screenshot to understand what
jumping of identity value is (It is taken from SQL 2014 Database table.)
Observe records below the red line..the identity value
jumped from 17 to 1014 and started continuing from there.
Cause: This is
not actually the issue or bug. It is by design in SQL versions from 2012
SQL Server was assigning values to identity column individually
like by adding 1 to the existing ID till SQL 2012 versions. From SQL 2012 the
SQL server allocating a batch of identity values like 1,000 or 10,000 numbers
to the cache to speed up the process.
When ID Jump happens:
when unexpected SQL services or server restart happens.
Why: There can be
many reasons but mainly when SQL Server is restarted without the database
containing the identity object being check pointed on shut down, any remaining
unused values in the cached range are lost, this caused the jumping.
Workarounds:
2)
Enable Trace Flag 272 in startup parameters.
Ø
Open SQL Server Configuration Manager.
Ø
Right-click SQL Server and select Properties.
Ø
In the opening window under Startup
Parameters, type -T272 and click Add, then press Apply button
and restart SQL Services.
Note: Shutting down SQL services in proper way prevents this scenario. shudown
2 comments:
This was an identified issue in Sql Server 2012 HADR that was fixed in a CU on SP2. You may want to check into something similar on Sql Server 2014.
Awesome post, extremely instructive. I ask why alternate pros of this segment don't see this. You should proceed with your composition. I'm certain, you have an incredible perusers' base already!e of sound sustenance and acknowledging nourishment. vSan 6 Enterprise for 1 processor
Post a Comment