Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Replicated data duplicating on subscriber? Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 8:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
Hey All,

I'm relatively new to replication and here's what I'm seeing across environments and SQL Versions (from SQL 2000 thru 2012):

After setting up transactional replication (snapshot every hour and replication every 2 minutes) in a PUSH mode from publisher to subscribers things seem to run just fine...most of the time.

Occassionally I'll do some spot checks or something will alert me that a closer look is needed and I'll find one or two published articles (or sometimes upwards of 10 articles) that are now 2X to 10X the size on the subscriber compared to the publisher. In some cases it's obvious that the records aren't being deleted before reinserted as I have row counts that are multiples higher than the publisher. In other cases I have the same rowcounts be the datafiles are completely off. The publisher datafile is about 10 megs and the subscription datafile is 10 GIGS...

Dealing with the first issue, I think, may help with the second issue.

What can cause one published article out of hundreds to go out of sync like that and then, once out of sync, to just continue to grow? Am I missing a setting somewhere?

If my problem is unclear or you need more information, let me know.

Erin
Post #1433234
Posted Wednesday, March 20, 2013 11:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 394, Visits: 1,974
Please check the options in the article properties. Probably you have disabled the option not to replicate the delete statements.

http://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/
Post #1433378
Posted Wednesday, March 20, 2013 11:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
As a follow-up example.

I have a table, call it tableA, that contains 1 million rows on my publisher and uses 103 MEGs of data space.
tableA in the subscriber database also contains 1 million rows and uses 11 GIGS of data space.

Neither the publisher or the subscriber tables have indexes or primary keys. They are structurally identical.

Thoughts? Help?
Post #1433379
Posted Wednesday, March 20, 2013 11:48 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
muthyala_51 (3/20/2013)
Please check the options in the article properties. Probably you have disabled the option not to replicate the delete statements.

http://www.mssqltips.com/sqlservertip/2520/options-to-not-replicate-sql-server-delete-commands/



Thanks, Muthyala, I will look into that for the articles who's rowcounts exceed the publishers..
Post #1433380
Posted Wednesday, March 20, 2013 12:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 394, Visits: 1,974
Erin Ramsay (3/20/2013)
As a follow-up example.

I have a table, call it tableA, that contains 1 million rows on my publisher and uses 103 MEGs of data space.
tableA in the subscriber database also contains 1 million rows and uses 11 GIGS of data space.

Neither the publisher or the subscriber tables have indexes or primary keys. They are structurally identical.

Thoughts? Help?


Weird !!!

Post #1433389
Posted Wednesday, March 20, 2013 12:31 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
lol... thank you. It's nice to be special.

I have this happening in more than one place as well. The SQL versions are identical, the structures are identity, the rowcounts are identical, yet the subscribers are massive compared to the publisher.
Post #1433408
Posted Wednesday, March 20, 2013 1:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 394, Visits: 1,974
Can you reinitialize the subscriptions with a new snapshot and let me know the result?

Post #1433453
Posted Wednesday, March 20, 2013 2:23 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
The result is what you would expect. The rowcounts match and data space used matches...for the first replication..

If I continue to for replicate it, to mimic the growth of a couple days, I find that the table size grows exponentially. Out of the transaction replication and the snapshot replication only the tables in the snapshot replication have this exponential growth. I DID notice that the snapshot replication is set to delete on the articles rather than drop and create..which leads me to another question:

If I change it to drop and recreate on snapshotted articles will the data space assigned to each article in the subscribing database reset? ie., will my 11 gig table release the unused data space when the table is dropped and recreated during snapshot and return to the size of 103 megs as it should be?

Thanks,
Erin
Post #1433483
Posted Wednesday, March 20, 2013 4:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2014 11:45 AM
Points: 275, Visits: 863
This is happening because you have heap tables.

Each time it applies a snapshot, the records get deleted and then inserted again.
It is very common not to get space back when you delete from heap tables.
Yo only get the space back if you truncate the table ( the extents get marked as unused)

If you use 'truncate' or 'drop' the problem will go away.
Post #1433533
Posted Wednesday, March 20, 2013 4:17 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:29 PM
Points: 541, Visits: 1,050
Excellent. That was the conclusion I had come to as well so I've changed the snapshot replication to drop and recreate and will report back tomorrow if that doesn't solve the issue.

Thanks folks!


Erin
Post #1433538
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse