Is there any effect of DB Mirroring on IDENTITY column / value of Tables ?

Question

Is there any effect of DB Mirroring on IDENTITY column / value of Tables ?

Viewing 7 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Kishore M. K. Mr or Mrs. 500 Points: 560 More actions · Answer 2

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

Kishore M. K. Mr or Mrs. 500 Points: 560 More actions · Answer 4

Kishore M. K.

Mr or Mrs. 500

Points: 560

January 20, 2016 at 4:22 am

#1853084

Please elaborate on the term "TF 272".

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

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!

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

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