September 15, 2008 at 11:30 pm
I am using row filter with transaction replication. I created a publication with 1 article, a table, and the row filter criteria set to WHERE YEAR(dt_CreateDate) = 2005. The published database's table is 303 million rows, using 37GB of space. When the subscriber finally finishes bulk inserting, the subscriber's table ends up with 100 million rows; which what is expected. But the space used on the subscriber for the table is 198GB.:w00t:
Why the huge increase in size?
Thanks,
Erik 🙂
September 16, 2008 at 5:01 am
What are the differences in the indexes? Is the clustered index on the subscriber fragmented?
September 16, 2008 at 9:49 am
There is only the PK Clustered Index going over. It has 0.00% total fragmentation.
The subscriber database's table shows 105Million rows using 184GB used
The publisher database's table for 300Million rows shows 37GB used.
This is a real head scratcher.
Erik
September 16, 2008 at 9:59 am
Verify that no data types are different between the two tables.
Figure out the size of your records (the sum of the space each field takes up based on it's data type) and multiply by the number of records. This should be the total space taken up by your table.
Here is a thought - do you have any large variable length columns? Did you end up with a bunch of white space in VARCHAR columns that is now taking up a lot more space?
September 16, 2008 at 11:42 am
The table consists of 4 columns: 2 int, 2 datetime
The mdf file size does indeed match the report.
September 16, 2008 at 1:07 pm
RESOLUTION: Fill Factor was 2% for some odd reason.
:D:D:D
Using the exec sp_mshelpindex command I got 2% as the OrigFillFactor.
Use SSMS, I set the Fill Factor to 100%, just for the best case scenario for the test and got:
115 Million rows
3,765,648 Reserved
3,755,775 Data
9,816 Indexes
56 unused
Now here is the question I have... Why did it happen?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply