DBCC CHECKIDENT post Sync

danbocdanboc Posts: 23
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?

Comments

  • 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.
  • 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.
  • 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
    
  • 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
  • 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.
  • 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
  • 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?
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
    David Atkinson
    Product Manager
    Redgate Software
  • danbocdanboc Posts: 23
    edited September 23, 2009 7:45AM
    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.
  • 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
    David Atkinson
    Product Manager
    Redgate Software
  • 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
  • Hi Dan,

    8.0.2 fixed the problem for us.

    Daniel
Sign In or Register to comment.