DBCC CHECKIDENT post Sync

danboc

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

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

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

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

CREATE PROCEDURE [dbo].[usp_sys_mnt_ReseedAllTables]
AS
BEGIN
	DECLARE @table NVARCHAR(4000), @column NVARCHAR(4000)

	DECLARE row CURSOR FOR
		SELECT	a.name AS TableName,
				b.name AS IdentityColumn
		FROM	sysobjects a
		INNER	JOIN syscolumns b 
		ON		a.id = b.id
		WHERE	COLUMNPROPERTY(a.id, b.name, 'isIdentity') = 1
		AND		OBJECTPROPERTY(a.id, 'isTable') = 1
		AND		a.xtype='U'
		AND		a.category=0
		ORDER	BY a.name
	OPEN row

	WHILE 1=1
	BEGIN
		FETCH NEXT FROM row INTO @table, @column
		IF @@FETCH_STATUS = -1
			BREAK
		
		PRINT @table
		EXEC sp_executesql N'DBCC CHECKIDENT (@table, RESEED)', N'@table varchar(4000)', @table = @table	
		PRINT ''
	END

	CLOSE row
	DEALLOCATE row
END

cklaw

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

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.

cklaw

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

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

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

cklaw

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

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

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

danboc

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

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

David Atkinson

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

danboc

Hi Peter, Hi David,

What I meant in my last post about there not being much point to an explicit value reseed is that the next sync will "fix" any discrepancies anyway, so why bother with an explicit value. What should be the primary concern is the "health" of the identity in the target table (i.e. executing a reseed WITHOUT an explicit value). An explicit reseed value is moot in the context of data syncing...

On the other hand if people really want the seeds to match then an explicit value should only be used if the tables are fully synced (both directions, all rows).

My tuppence.

Daniel

PS Thanks again for the attention to this matter.

David Atkinson

You're right. Others have suggested that a simple non-explicit reseed and we may add this as a suboption if reseeding has been enabled. But we'll definitely consider replacing the explicit reseed with this behaviour if we don't find any evidence that the explicit option is a real requirement.

Thanks for raising this issue. I think the debate was definitely due.

David

Random Lengths

I'm having a very similar problem, though it seems like it only happend one time.

I get the same PK failure mentioned above, on my table of 35 million rows.

I run a dbcc checkident (tablename) and get

Checking identity information: current identity value '35364482', current column value '35367482'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I know I had run a Data Compare and sync earlier that day on a different table, and a data compare and sync on that table a couple of weeks ago.

so I reseeded and all is well.

I use SQL Data Compare 8 all the time and on many db's, and this is the first and only time this happened.

I downloaded 8.0.2 per the update feature. Is this kind of thing resolved?

Dan

danboc

Hi Dan,

8.0.2 fixed the problem for us.

Daniel