SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replicated data duplicating on subscriber?


Replicated data duplicating on subscriber?

Author
Message
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2273 Visits: 1119
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
muth_51
muth_51
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2661 Visits: 2934
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/
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2273 Visits: 1119
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?
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2273 Visits: 1119
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..
muth_51
muth_51
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2661 Visits: 2934
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 !!!
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2273 Visits: 1119
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.
muth_51
muth_51
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2661 Visits: 2934
Can you reinitialize the subscriptions with a new snapshot and let me know the result?
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2273 Visits: 1119
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
arnipetursson
arnipetursson
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1445 Visits: 1019
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.
Erin Ramsay
Erin Ramsay
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2273 Visits: 1119
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search