September 12, 2008 at 8:13 am
Hi i have Replication from SQL 2005 from DatabaseA to SQL 2005 Second server SQL 2005.
The replication data was out of sync after i rolled one of my cluster overs.
So i tried to get the data insync again by clicking on the Replication Monitor and doing a
reinitialize all subscriptions - use current snapshot - mark for reinitialize.
What i noticed was that in the REPLICATION Database B
TABLE A
TABLE B
TABLE C
Whilst it was running this the TABLE B TABLEC physically were dropped - therefore causing our
reports to time out.
Is there a way to resync the data again from scratch without the tables being dropped in the process on the REPLICATION DatabaseB.
I do not mind them being dropped if new schema applied but this wasn't the case the data just got out of sync.
Cheers
I found this http://msdn.microsoft.com/en-us/library/ms162843.aspx
But i cannot get it to generate fix script.
TableDiff.exe -sourceserver "xxx" -sourcedatabase "TEST" -sourcetable "FRUIT" -destinationserver "xxxxxx" -destinationdatabase "test" -destinationtable "FRUIT" -f -q -o c:\tablediff\test2.sql
Just reports:
Table [TEST].[dbo].[FRUIT] on AFHC-FCSCYB1\ASRCFHSQL2005 and Table [test].[dbo].[FRUIT] on AFHC-FCSCYB1\ASRCFHSQL2005_2 have different row counts.
Table [TEST].[dbo].[FRUIT] on AFHC-FCSCYB1\ASRCFHSQL2005 has 3 rows.
Table [test].[dbo].[FRUIT] on AFHC-FCSCYB1\ASRCFHSQL2005_2 has 2 rows.
The requested operation took 0.2967059 seconds.
So this would not actually fix the subscription rows.
September 15, 2008 at 10:54 pm
If you look at the test2.sql file that you created (although you have -q and -o immediately after -f, so not sure if this would prevent the file from being created) then this should have the update or insert commands required to have your tables synchronised. You need to run it on the db that has the missing records.
You may need to turn off the check constraint before inserting the records (I needed to do this before resolving a similar issue on my DB).
ALTER TABLE TableName NOCHECK CONSTRAINT ALL
--Run entity insert statements here
ALTER TABLE TableName CHECK CONSTRAINT ALL
September 16, 2008 at 6:27 am
Ok i got the file created.
Now if you run it to update just a single table in the subscriber this wouldnt have a problem with the replication.
Now the script is good for single table - how do you suggest you run it for all tables otherwise you have to determine which counts are off. That could be time consuming.
September 16, 2008 at 4:17 pm
Sorry, I don't know of a way to do it for all tables at once, only one table specified at a time
When I had a similar issue I only had about 6 tables that were not in synch, so it was no big deal to run tablediff 6 times
If you have a lot of tables out of synch, maybe there is a better solution than tablediff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply