March 20, 2013 at 8:04 am
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
March 20, 2013 at 11:46 am
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/
March 20, 2013 at 11:47 am
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?
March 20, 2013 at 11:48 am
muthyala_51 (3/20/2013)
Thanks, Muthyala, I will look into that for the articles who's rowcounts exceed the publishers..
March 20, 2013 at 12:03 pm
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 !!!
March 20, 2013 at 12:31 pm
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.
March 20, 2013 at 1:30 pm
Can you reinitialize the subscriptions with a new snapshot and let me know the result?
March 20, 2013 at 2:23 pm
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
March 20, 2013 at 4:02 pm
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.
March 20, 2013 at 4:17 pm
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
March 21, 2013 at 5:53 am
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?
Transactional replication requires primary key.
SueTons.
Regards,
SQLisAwe5oMe.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply