We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table using an SSIS data flow. The table has a bigint Primary Key and rows are being deleted in numerical order of this key. As I am deleting rows, however, the overall Index size of the table is steadily increasing, and I cannot figure out why. The data size is remaining the same throughout this process.
Here are the table details:
Row Count: ~300,000,000
Data Size: ~65 GB
Index Size: ~65 GB
Rows are being deleted out at a rate of ~350,000 per hour.
The column definitions use the following data types: smallint, int, bigint, char, varchar, nvarchar, uniqueidentifier, bit, datetime
The table has one Primary Key which is the Clustered key on the table as well as 4 non-clustered indexes.
The table is also part of a replication maintenance plan. The replicated copy of the table is decreasing in both Data Size and Index Size!
I performed the same process on another table last week and I could see both the Data Size and Index Size of that table decreasing as I deleted rows.
Is there any explanation as to why the Index Size keeps going up in this case?