January 20, 2016 at 2:14 am
In presence of Witness Server, I am configuring Principal & Mirroring Server for Database Mirroring. The Database which is to be mirrored have multiple tables having IDENTITY Column mostly treated as PK.
When Principal Server got DOWN, Roles changed by Witness Server i.e. Principal DB became Mirror and Mirror DB became Principal.
Application Server configured to connect new Principal DB.
When verified for newly created very few (3 to 4) transactions on new Principal DB, the respective Table's IDENTITY value increased by approx. 10000.
When Principal Server got UP, Roles again changed by Witness Server to it's original.
When verified for newly created very few (3 to 4) transactions on Principal DB, the respective Table's IDENTITY value again increased by approx. 10000.
Is there any effect of DB Mirroring on IDENTITY column / value of Tables ?
January 20, 2016 at 2:59 am
It has nothing to do with mirroring, it's the default behavior of identity columns in SQL Server 2012 and up. Identity columns are preallocated and when the database goes offline then online, the preallocated pool is lost and has to be reinitialized, thus leaving gaps in the column.
It should not be a big deal, because the application shouldn't be relying in "no gaps" assumptions for surrogate keys, which, as surrogates, should not be used by the application or displayed to users at all.
-- Gianluca Sartori
January 20, 2016 at 3:38 am
Thanks Gianluca Sartori for your reply....
I agree for the IDENTITY value gaps during SQL Server Roles swapping.
But it had the huge difference of 10000.
Just wanted to know the exact cause of it.
Is there any way to avoid the gap .. so that the IDENTITY value will be in sequence after any Roles swapping.
January 20, 2016 at 3:52 am
Indeed the default identity preallocation is 1000, 10K looks suspicious. Try enabling TF 272 (it disables identity preallocation) and see if it makes a difference.
-- Gianluca Sartori
January 20, 2016 at 4:22 am
Please elaborate on the term "TF 272".
January 20, 2016 at 4:45 am
Trace flag. Add "-T272" to your SQL Server startup parameters or enable it globally before adding to the startup params:
DBCC TRACEON(272,-1)
-- Gianluca Sartori
January 20, 2016 at 6:34 am
spaghettidba (1/20/2016)
Indeed the default identity preallocation is 1000, 10K looks suspicious. Try enabling TF 272 (it disables identity preallocation) and see if it makes a difference.
I seem to remember someone commenting on one of the connect items about this shortly after 2012 was released that 10000 is the preallocation amount for bigint.
If that's true, it might just be that the column is bigint. My memory's fuzzy, though, so I'll see if I can track down the source of the claim in a bit.
Cheers!
January 20, 2016 at 6:41 am
Jacob Wilkins (1/20/2016)
spaghettidba (1/20/2016)
Indeed the default identity preallocation is 1000, 10K looks suspicious. Try enabling TF 272 (it disables identity preallocation) and see if it makes a difference.I seem to remember someone commenting on one of the connect items about this shortly after 2012 was released that 10000 is the preallocation amount for bigint.
If that's true, it might just be that the column is bigint. My memory's fuzzy, though, so I'll see if I can track down the source of the claim in a bit.
Cheers!
Thanks! A quick Google search confirmed your suspicion.
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply