DBCC CHECKIDENT post Sync

danboc · Joined: 01 May 2008 Posts: 16

I am not sure this is a bug though it seems like one to me.

After syncing data for a table which has an identity column, SQL Compare will NOT run DBCC CHECKIDENT (RESEED) so that the next insert on the target table will crash as the identity column is out of sync.

Anyone else have this issue?

cklaw · Joined: 22 Sep 2009 Posts: 12

Incredible that we should both discover this problem and post the same day.

Over the last couple of months, I have been plagued by users contacting me with exceptions being thrown because of PK constraint violations. When I look at the identity and seed on the table(s) in question they are different. I reseed them and later the problem pops up on another table.

These are tables that I keep in sync with SQL Data Compare 8, and I have eventually created a simple test to demonstrate the fault.

Create an identical table in two databases. It only needs an identity column and one extra column, a varchar(50), say. Make the identity column the PK. Add some rows to one table (call it the left-hand table) and different, greater number of rows to the other (right-hand table).

Now, compare the tables with SQL Data Compare. Select to sync from left to right, but only to update the rows that are different, not to delete any rows on the right. Select Synchronise and then look at the script that's generated. It includes a RESEED, even though no rows are being deleted.

Allow the synchronise to go ahead and then check the identity on the right-hand table. You will find that the current identity value is lower than the current column value, so the next insert from code will fail.

I'm off to put this all in an e-mail to RedGate now.

cklaw · Joined: 22 Sep 2009 Posts: 12

In fact, it's even worse than I thought. Now that I know where the problem lies, I ran a compare on multiple tables. Four tables had different rows. I selected just one table to synchronise and looked at the script. There was a RESEED for a table that I hadn't selected to sync.

I then selected the last two tables with differences instead, and checked the script. There was no reseed on these two tables, but the reseeds on the original two were still there, even though I had not selected them this time.

I'm amazed that no one else has had a problem with this yet. Or perhaps they have and they just don't realise where it is happening.

danboc · Joined: 01 May 2008 Posts: 16

Hey there cklaw,

Until we hear back from RedGate, we've written an SP which we run after compares. It basically goes through all our tables and reseeds them. I've attahced it below.

Have a good one!

D

cklaw · Joined: 22 Sep 2009 Posts: 12

Hi D

That was going to be my next task. Thanks, you've save me a job. Can you think of a neat way to determine which tables need reseeding; I want to get a list of affected tables before I reseed them?

Charles

Brian Donahue · Joined: 23 Aug 2004 Posts: 5771 Location: Red Gate Software

Hi Charles,

We have got a private build that will not reseed tables that are not included in the synchronization -- I believe it will still reseed when a table has been selected but there are no differences after the data comparison, so the update probably meets you half-way.

For the next version of Data Compare, we plan to make the reseed optional and introduce some other changes to do with identities.

I am sending you both a Private Message with the link to the private build.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

cklaw · Joined: 22 Sep 2009 Posts: 12

Hi Brian

Thanks very much for the quick response. I will give it a try. Can you indicate when the next version might be available? I'm just a bit nervous now because I realise the havoc that has been caused in a couple of our databases, and whilst I will check the script now every time I'd hate for anything to slip through again.

Charles

cklaw · Joined: 22 Sep 2009 Posts: 12

I have just tried the private build, and it is undoubtedly an improvement as it also no longer removes and re-adds FK constraints on tables that are not being synchronised. However, it still only goes half-way, as you indicated. When I select to update but not add or delete, it will still reseed and get the identity wrong. Is there any chance of a private build that includes this extra bit of logic?

peter.peart · Posted: Tue Sep 22, 2009 11:34 am Post subject:

Hi Charles,

Can I confirm, how is the reseeding wrong?

My colleague Brian has mentioned that the private build is available which will stop reseeding on tables that you have not selected for the synch, however it will reseed on anything that you have included even if there are no differences, and I believe this is the default behaviour of the engine.

If you are expecting that the tool does not reseed on tables included in the synch where there is no difference that is one thing, and if the tool is messing up reseeding in on shape or form that is something different.

If the reseeding is getting messed up, please can you provide me with a little more information regarding what is happening?

We also have a support call open for you regarding this, so if you would prefer to deal with this via that mechanism let me know.

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

cklaw · Joined: 22 Sep 2009 Posts: 12

Hi Peter

It would be easier for me to respond via the support mechanism, if that's alright. Will you send me an e-mail?

Thanks

Charles

danboc · Joined: 01 May 2008 Posts: 16

Hi Peter

Charles' first reply to me shows clearly how this happens and I can second the fact that it does happen... If there is anything you would like me to test on our site, please let me know.

On another note, I would like to point out your extraordinary support. Receiving a private build so quickly has really impressed me and my team! Keep up the good work!

Daniel

peter.peart · Posted: Tue Sep 22, 2009 12:34 pm Post subject:

Hi Daniel,

Many thanks for your reply and for your feedback, much appreciated! Are you still experiencing issues with the private build or has this resolved any problems for you?

Charles: I will send you an e-mail now regarding this.

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

danboc · Joined: 01 May 2008 Posts: 16

Hi Peter,

There is still a problem. Example:
- SOURCE table has a current identity of 100;
- I choose row IDs 1-50 for the sync;
- At the end of the sync the CHECKIDENT will reseed with an explicit value of 100! If the TARGET table had IDs 101-200 then the next insert will crash.

My suggestion: Do not set an explicit value for the reseed. There is no point if you think about it.

Daniel

peter.peart · Posted: Wed Sep 23, 2009 9:58 am Post subject:

Hi Daniel,

Are you updating records in the target database or adding records to the target?

There is of course the ability to NOT reseed anything, however reseeding is turned on by default. You can remove this by going to the Project Configuration screen and looking in the Options tab.

Going forward, Charles has requested the ability to decide whether reseeding is done on a table by table basis, and this has been logged as a feature request with a reference number of SDC-1056.

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

David Atkinson · Joined: 05 Dec 2005 Posts: 921

Hi Daniel,

The reason why we reseed explicitly is because in some cases users want the seeds to match on both sides. However we recognise that this can cause more problems that it solves especially when the data in the source doesn't closely match that in the target, and therefore we now have a change request in place to ensure that the option is unchecked by default.

Kind regards,

David Atkinson
Product Manager
Red Gate Software

	All times are GMT + 1 Hour Go to page 1, 2 Next
Page 1 of 2