Reset IDs... TAKE CARE!

[Paolo F Cantoni]

I accidentally pressed the GO button on the Reset Auto Increment Columns dialog while having the (@) Reset All Tables option selected. 

I've never used this dialog before and fortunately this was on a clone - not the production repository.

It's going off restarting each auto-increment column from 1, row by row (which is what one would expect).  Since it's a very large repository it will take a long time.

My questions are: 

• I assume it's NOT a good idea to do this while others are on the repository (or is it smart enough to handle this)?
• I further assume that once started, it must run to completion - otherwise if interrupted, results are unpredictable.

Can this be confirmed please...

Paolo

[Eve]

As far as any concurrent users are concerned, it will be very similar to if you had performed an XMI export to updated everything in the model. Or like you deleted everything and recreated it. Typically this doesn't break EA, but they can lose edits etc.

It looks like each update that gets performed is done in a database transaction with the rows that are referencing it. If anything goes wrong that update is rolled back. Shouldn't be a disaster if it does get interrupted, but I'd certainly recommend letting it finish if possible.

[Paolo F Cantoni]

As far as any concurrent users are concerned, it will be very similar to if you had performed an XMI export to updated everything in the model. Or like you deleted everything and recreated it. Typically this doesn't break EA, but they can lose edits etc.

It looks like each update that gets performed is done in a database transaction with the rows that are referencing it. If anything goes wrong that update is rolled back. Shouldn't be a disaster if it does get interrupted, but I'd certainly recommend letting it finish if possible.

Thanks Simon,

In over an hour it had only processed 30K of 90K connectors - so I killed it.  I need to load a different snapshot and couldn't wait the time to let it finish (>24 hours at that rate?)

Another time, I might run it to see how long it actually takes.

[Paolo F Cantoni]

So, we tried a complete Enterprise-scale reset of ALL ids on a clone of our production SQL server repository.  It took OVER 12 hours!

We decided on a different tack...  We tried a local EAP clone and found it took about 2 hours!

Transfer to local EAP file (10 mins), Reset IDs in the EAP file (2 hours), transfer back to SQL Server (about 1.5-2 hours.  Total time 4 hours!  This happened over the weekend during some scheduled repository downtime.

All SEEMED to go well until we decide to add some new objects this morning... 

We then discovered that even though we had compacted ALL the IDs (and the Reset IDs functionality forces a compact of the EAP file to re-align the internal ID sequencers), the transfer project back to SQL Server does not.  So we now have a gap from 70003 for the last object from last week and 333213 for the first object this week.  Effectively, the Reset IDs was for nothing.  This pattern is repeated for all the tables with gaps.

In Transfer Project to .EAP and Compaction, I discuss (essentially) the same problem in the reverse direction.  This current behaviour shows the same problem exists in the this direction.

Basically, if you do a transfer project, I can't see any point in NOT resetting the sequencers to the LAST used value for each table.

So now what to do?  Do we repeat the process, but before transferring back to SQL Server, transfer up an empty project and then Reset those IDs?  Then, transfer the real reset project?  Will this update the sequencers correctly?

Paolo