0

I have transactional replication with updatable subscriptions going between a few SQL 2008 R2 servers (publisher is Enterprise, subscribers are Express).

I need to add another subscriber, and come to discover that my database has outgrown the 10GB limit for Express. My current subscribers are under the 10GB limit, however the publishing database is 13GB.

So I delete some large unused columns and data from the largest tables, run dbcc cleantable, run update statistics on them, the tables go down in size a bit and I thought I was good to go!

However, the publishing database is still a good 11.5GB while the subscribers all went down to 8GB.

I compare table sizes between the publishers and subscribers and the few largest tables that I had deleted data from are larger in the publishing database than the subscribing databases - by a couple gigs.

I compare table structures and use RedGate's Data Compare - the tables are identical between the publisher and subscribers so I am at a loss. I don't know what is cause the discrepancy let alone how to resolve it so I can add another subscriber (without having to buy SQL Standard licenses for the subscriber). I have a feeling it has to do with being the publisher and it's row-count has grown significantly within the last year.

As a side note - I do also have a couple SQL Standard 2008 licenses, however they're 2008, not 2008 R2 therefore incompatible to initialize the subscriber using a backup. The sites have slow connections so I have always initialized replication from backups.

happydba
  • 1
  • Are the fill factors and pad_index settings on all of the indexes on the tables on both sides are the same? Do all of the indexes exist on both sides? Either way, sys.indexes is the place to look for both questions. – Ben Thul May 10 '13 at 23:26
  • Also, if your other subscribers are under your threshold, can you init with a snapshot instead of a backup? That should let you sneak in under the wire. – Ben Thul May 10 '13 at 23:29
  • The indexes are only 288K. The tables are heaps. I had tried creating a clustered index, rebuilding the indexes, removing the clustered index, and the table data size remain the same – happydba May 10 '13 at 23:29
  • Ah I looked that up, but I cannot initialize from a subscriber :( – happydba May 10 '13 at 23:30
  • And the unused space is also only 2456K – happydba May 10 '13 at 23:31
  • I'm not suggesting that you init from a subscriber. Somehow, that data takes up less room at the subscriber than the publisher. I say you just add the subscriber and init it with a snapshot and see what happens. The worst that happens is that it fails for running over your 10 Gb limit at which point you drop the subscriber go back to the drawing board. – Ben Thul May 10 '13 at 23:37
  • Ahhh yes that was going to be my next try over the weekend, while the system wan't in use... I will update how that goes – happydba May 10 '13 at 23:40
  • Solved it (sort of). In desperation, I made a copy the table on the publisher and voila! the table size cut itself in half. So I will have to disable replication for that table, switch out the tables, and re-enable replication on it. Not sure why it's behaving that way but... it is what it is! Thank you! – happydba May 13 '13 at 15:09

0 Answers0