I have a handful of tables in SQL Azure. The two most active tables both have id columns that are identity columns and primary keys. BOTH tables have been skipping large chunks of ids when adding records. For example:

424
1423
1424
1425
1426
2424
2425

What gives? Is this a known issue? I'm somewhat horrified at this point. My app is in no way under heavy load; I can't imagine what will happen if I actually get some traffic. Any help is appreciated. Thanks!

I have the same issue on my production application and i am not able to debug what might be causing it.. can anyone help please..

I read that it is something to do with locking... not sure still spinning in circles

I have not seen this issue on any of my databases. The scheme of the database and architecture of the application and whether any triggers or constraints exist would provide further insight.

Is it possible that the application is trying to insert into the table a value that already exists and violates a constraint? Any attempt to insert into a table typically results in the identity column incrementing, whether the interstation is rolled back due to error or not. Identity columns are guaranteed to increment, but not necessarily gap free.

Information from msdn, take note of the last bullet: http://msdn.microsoft.com/en-us/library/ms186775%28v=sql.110%29.aspx

The identity property on a column does not guarantee the following:

• Uniqueness of the value Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

• Consecutive values within a transaction A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

• Consecutive values after server restart or other failures SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.

• Reuse of values For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

Thanks for your feedback Corey, i am looking at it now

Hi Corey- Thanks for the reply, I like some of your ideas. I'm leaning against the constraint violation, but am going to test to make sure I'm not missing something.

In the meantime, here is the definition for one of the problem tables (below); could the ALLOW_ROW_LOCKS = ON be the issue?!?!?! I hadn't noticed that when I was creating the tables, but it is obviously suspect. I would still be shocked if that was the problem, considering that there are day stretches in between inserts where the gaps are (ex below the table def). I wouldn't think it would have a need to lock, but maybe the azure structures are such that this could be a problem?

Thanks again!

(vishalishere - good to know I'm not alone!)

CREATE TABLE [dbo].[Photo](
    [PhotoId] [int] IDENTITY(1,1) NOT NULL,
    [FileGUID] [varchar](50) NOT NULL,
    [Caption] [varchar](140) NOT NULL,
    [ObjectId] [int] NOT NULL,
    [ObjectTypeId] [int] NOT NULL,
    [AppUserId] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_WebImage] PRIMARY KEY CLUSTERED
(
    [PhotoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ID        Create Date

3426    2013-06-01 22:54:42.970
3425    2013-06-01 14:08:44.157
2425    2013-05-28 11:56:25.527
2424    2013-05-25 21:03:18.343
1426    2013-05-20 08:35:06.687
1425    2013-05-20 07:37:10.783
1424    2013-05-20 07:05:35.213
1423    2013-05-17 01:28:01.960
424    2013-05-07 19:06:59.787

not only we have been facing the common issue, coincidentally my table also has to do with photos and store similar data as your table do.

strange isnt it..

 

Strange or tragic, depending on how you look at it.

By chance, when you created your tables in Azure, did you use a script generated from a traditional SQL 2XXX server? I'm wondering if the primary key hints aren't appropriate for Azure.

Very frustrating. I wish I had access to full support!

I'm also wondering if our SQL server instances are bouncing to the extent that they are loosing their current/last identity value (as described in the MSDN article that Corey pointed to).

That also would be very disappointing.

So I bit the bullet and paid for a month of support; here is the official answer I got:

I checked that this issue is by-design in SQL 12 (on-premises and WASD). Tables with identity columns will have the identity reseed to a larger value (creating range gaps) after SQL Server Instance is dirty-restarted.

There is no known workaround.

This is sooo appalling. I understand the reseed need, but why the hell is the instance bouncing that much?

I have more questions in. I will keep you posted if I learn more.

Thanks for your update glabs, i really appreciate your response, i feel to change my id column and generate id on my own.. i fear that with this reseed i may soon hit the upper limit on my column size..

I feel your pain vishalishere<abbr class="affil"></abbr>! You know I just looked at another DB of mine hosted at Azure and I'm not seeing the problem there at all. Though only difference between the two would be that

1) The first (non failing) db is under lighter load and 

2) I didn't seed that DB at all

Whereas, the db that is failing IS under load (but not much) AND I seeded it initially with < 1000 records.

I'm wondering if you had seeded your db as well? I'm trying to triangulate here.

Thanks

I have the exact same problem by using an application which is for management only. So I am the only user using it, it has kind of ZERO load.

Our autoincrement ID suddenly jumped from 327 to 10327. 

On top of this "DBCC CHECKIDENT is not supported in SQL Azure", now that is really annoying as we now have to recreate the table, delete the false entries and edit the autoincrement value by hand while importing.

For me this is a critical problem which does not make Azure a reliable host for our database. Would be great to have any infos on this, otherwise we have to move away from it.

Thanks and kind Regards

Andreas

Thanks for chiming in Andreas. I think that if there are enough of us complaining, we will get some answers, and hopefully a solution. I suspect (hope) that it is something insignificant and that a fix, or instructions on how to fix, will be forthcoming.

i agree completely

Joining in the party as I'm experiencing the same behavior.

And BTW, this seems to be related: 

https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity

I'm having the concerns for my Azure SQL tables, where the identify value jumps in some cases by up to ~10,000. I read the link given by @Pedro Mag. Can someone provide me with an example on how to apply this "trace flag 272" work-around? In other words, how would I use the trace flag 272 work-around with existing tables? Would it be something that goes in my existing procs before calling inserts on those tables? Thanks.

Hi Guys,

This is why i love forums, collaborative and solved the issue

for those who want to know how to add Trace flag 272 here are the steps

1. Open "SQL Server Configuration Manager"
2. Click "SQL Server Services" on the left pane
3. Right-click on your SQL Server instance name on the right pane
4. Click "Properties"
5. Click "Startup Parameters"
6. On the "specify a startup parameter" textbox type "-T272"
7. Click "Add"
8. Confirm the changes

I am still evaluating if I should opt for tf 272 or use a sequence generator with NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx) as this documentation says Trace flags may not be supported post SQL 2012

here is a link with screen shots and a script to do the same 

For curious ones here 

272 will cause a log record to be generated for each generated identity value as in previous versions. this generated log is the one that prevents gaps from happening.

Vishal - do you know how this would be applied for an Azure-hosted database? I've only used VS2012 and the Azure web app to manage tables/procs. Can these steps be done through either VS or the Azure interface? Thanks.

take a look at the script "here is a link...."

I guess you would be able to run the script from the SQL Azure portal, or you can use sql server 2012 management studio to connect to your sql azure and then change the settings..

Vishal- Wow this is exciting. Have you seen any further gaps since you implemented the fix?

FYI my support ticket is being transferred over to the SQL Azure team. I am still curious to hear what they say and will share it with everyone here if it seems relevant.

And I think there is still a mystery here. Why are our SQL instances bouncing so much? I can understand a website instance, but the database? That seems problematic.

Thanks. I'll take a look, although the script looks a little scary with my limited knowledge. The last thing I want to do is mess up my Azure database.

Given this new way MS is seeding the database, is the recommendation to always use a BigInt now for primary keys instead of an Int? I wouldn't be as concerned about the skipped values with a bigint.

@Glabs, I am not comfortable with the TF 272 options specially beucase there is no guarantee that it will be supported in future versions and highly likely it wont be,

at this point I am leaning more towards opting for creating a sequence generator, at least things will be in my control.

Good news (sort of); I talked to somebody at MSFT and he cleared the air a bit. I will share with you what he said:

- The TF 272 option won't work in Azure

- The behavior (reseed) is by design, but has been acknowledged internally as less than optimal and a request has been made (again, internally) to change the behavior. This may or may not happen.

- The reseed is triggered by instance bounces, which are covered by the SLA. They are mostly patches to the OS or SQL Azure itself. 

The most important point was that, chances are, we will never hit the int limit. I think we all are forgetting (at least I did) that SQLAzure is not like SQL Server; there are very real limits in place, specifically total db size (150 gigs). He also said there is a max row limit per table of 10 million records, but I'm not finding documentation of that on the web. Assuming that is correct, even with jumps of 1000k, we would still be safe. And yes you could also switch to a bigint if you hit the int limit before the total db size limit. His point was simply that we will run out of room before we hit the int limit.

It isn't ideal for sure, but I'm no longer concerned about hitting an int limit.

Hopefully that helps some people here.