Identity column gaps -- time based?

Question

Identity column gaps -- time based?

Dird

SSCertifiable

Points: 5081
More actions
June 6, 2014 at 12:51 am

#292146

Hi,
For a while now we've been having issues with an identity column jumping large gaps. We didn't notice until it reached the end of the INT range, then we saw the gaps and set the value to within a large gap in the range.
The problem has persisted (occassionally hitting identity values that already exist) so we set up insert/delete triggers yesterday to see if the gaps are from large deletions. This isn't the case as there's only 300 deletes compared to 7000 inserts. Looking at the data there is a correlation between the time between inserts and the size of the entity column gap. Has anyone seen such an issue occur before? Here's a sample of the PK (ItemID) gaps, it's a small sample here but the rest of the inserts match with this kind of correlation:
Time gapItem GapitemID
1006637744
00:01:01.56637101006641454
00:00:59.27426521006644106
00:02:00.42663611006650467
00:02:00.07750931006655560
00:03:58.173114541006667014
00:04:00.000122741006679288
00:00:00.410731006679361
00:00:00.270421006679403
00:01:59.97065271006685930
00:01:59.95064731006692403
00:01:00.23426021006695005
00:00:00.57611006695006
00:00:00.37411006695007
Thanks for any info
Dird

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Are there a lot of failed transactions against that table?

If a transaction is initiated, an identity value is allocated. If the transaction fails, the allocated identity value is "lost".

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Dird SSCertifiable Points: 5081 More actions · Answer 2

Hi,

I feel this is unlikely because at the point there was 2,300 rows inserted the gap between first & last was 2.6 million (that's a lot of failed transactions); how would look for failed transactions though? Is there a dmv? Errorlog comes up empty.

Dird

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

There is no easy DMV to look for failed transactions.

You need to have some monitoring in place.

http://dba.stackexchange.com/questions/44836/get-transactions-that-failed-or-were-never-committed

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Dird SSCertifiable Points: 5081 More actions · Answer 4

Is fn_dblog likely to cause any performance issues? So far it's 9 minutes & still running for the following:

SELECT top 1 *

FROM fn_dblog (NULL, NULL)

WHERE Operation = 'LOP_ABORT_XACT';

Edit: I gave up after 32 minutes with no result returned.

Dird

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SQL 2008 (as per the forum) or SQL 2012?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dird SSCertifiable Points: 5081 More actions · Answer 6

GilaMonster (6/6/2014)
SQL 2008 (as per the forum) or SQL 2012?

2008r2

Dird