none
SQL Server replication and size differences of source and destination databases RRS feed

  • Question

  • I set up snapshot replication for a DB between two SQL instances.  On the source instance, the DB shows as 106612.56MB with 34663.75MB as available free space.  I expected that the replica would then end up being 71948.81MB (106612.56 - 34663.75 because it wouldn't replicate the white space).  The resultant replica database is showing as 35522.94MB.  The required data appears to be present in the replicated DB as the SSRS reports that use it are able to find the data they look for.  But why the large discrepancy in size between the source and replicated DB?  The replicated DB is less than 1/2 the size of the source DB.  I've searched around and can't seem to find any explanation.  I realize this isn't mirroring so the DBs will not be identical in size but I did not expect to see such a large difference between the two.  I am replicating all almost all articles (tables, stored procs, etc.) with the exception of a handful of stored procedures and user-defined functions that either reference invalid column names in a table (vendor bug) or reference another DB that is not present on the replica's instance.  I would expect these 4-5 articles can not account for a 37000 MB size difference between the two DBs.

    Please note that this has nothing to do with transaction log size.  I am specifically talking about the database size and am not looking at the size that combines both DB and TxLog size.

    Any insight?

    Wednesday, February 26, 2014 4:57 PM

Answers

  • Hi,

    One thing that might trick the huge difference is the absence of indexes in the replicated version of the database.

    When replicating the articles, you might not have selected to copy nonclustered indexes - and these _might_ take up a lot of Space. By default only clustered indexes are copied.

    Another thing may be the fact that when replicating the data, your freespace in the individual pages are almost 0 in average. Depending on the interval of you index rebuild on the source server, this is most certainly not the case there.

    Regards

    Rasmus Glibstrup, SQLGuy

    http://blog.sqlguy.dk

    • Proposed as answer by Tom Phillips Thursday, February 27, 2014 1:26 PM
    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Wednesday, February 26, 2014 8:42 PM
  • As a default non-clustered indexes do not get replicated.  This would explain the difference in size.

    • Proposed as answer by sysadminsa Thursday, February 27, 2014 2:38 AM
    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Wednesday, February 26, 2014 8:46 PM
  • Just as Tom said, the non-clustered index may be the root cause. Just check the objects size in both of the source db and the replica db, you will find the missing objects.

    彼节者有间,而刀刃者无厚;以无厚入有间,恢恢乎其于游刃必有余地矣!

    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Thursday, February 27, 2014 2:39 AM
  • Another factor could be that on the publisher the data is distributed through pages, paragraphs and extents. Depending on your fill factor and the amount of deletes and your datatype, there could be space in the pages, paragraphs and extents which have not been reclaimed.

    During the bcp process which is part of the snapshot application process on the subscriber all the data will be in the tables in a contiguous fashion. I would suspect this would be why you have the difference in space usage.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Thursday, February 27, 2014 2:17 PM
    Moderator

All replies

  • Hi,

    One thing that might trick the huge difference is the absence of indexes in the replicated version of the database.

    When replicating the articles, you might not have selected to copy nonclustered indexes - and these _might_ take up a lot of Space. By default only clustered indexes are copied.

    Another thing may be the fact that when replicating the data, your freespace in the individual pages are almost 0 in average. Depending on the interval of you index rebuild on the source server, this is most certainly not the case there.

    Regards

    Rasmus Glibstrup, SQLGuy

    http://blog.sqlguy.dk

    • Proposed as answer by Tom Phillips Thursday, February 27, 2014 1:26 PM
    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Wednesday, February 26, 2014 8:42 PM
  • As a default non-clustered indexes do not get replicated.  This would explain the difference in size.

    • Proposed as answer by sysadminsa Thursday, February 27, 2014 2:38 AM
    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Wednesday, February 26, 2014 8:46 PM
  • Just as Tom said, the non-clustered index may be the root cause. Just check the objects size in both of the source db and the replica db, you will find the missing objects.

    彼节者有间,而刀刃者无厚;以无厚入有间,恢恢乎其于游刃必有余地矣!

    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Thursday, February 27, 2014 2:39 AM
  • Another factor could be that on the publisher the data is distributed through pages, paragraphs and extents. Depending on your fill factor and the amount of deletes and your datatype, there could be space in the pages, paragraphs and extents which have not been reclaimed.

    During the bcp process which is part of the snapshot application process on the subscriber all the data will be in the tables in a contiguous fashion. I would suspect this would be why you have the difference in space usage.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by ceestep Thursday, February 27, 2014 7:32 PM
    Thursday, February 27, 2014 2:17 PM
    Moderator
  • Thanks guys.  You were all right for the most part.  I had figured out yesterday that the NCIs were missing once it was reported that the reports running off those replicated databases were taking forever.  Even after adding them in the publication, the size of the indexes were still smaller than they were in the source but I guess than can be attributed to the fill factor which in the source was set to 90 and the replicated version has a fill factor of 0.
    Thursday, February 27, 2014 7:31 PM
  • Thanks for the info on the fill factor!
    Thursday, February 27, 2014 7:32 PM