February 22, 2011 at 5:20 pm
Please excuse me if this is posted in the wrong section or has been answered before. I am new to this site and SQL in general for that matter.
I have developed an SQL 2005 database and data is populated into the sales table via a DTS batch. Previously the rows per batch size was around 3k, but in order to correctly redirect error output, I reduced this to 1 row per batch, so that each row would be handled individually and moved to the flat file error output if the insert failed.
The destination table has a clustered index of three columns to prevent duplicates.
Upon making this change to limit the rows per batch to 1 in the DTS package, the MDF file has started growing dramatically. I noticed the change from the nightly backup.
Running sp_spaceused on the database before the change gave the following
database_size =4582.44 Mb
unallocated space = 81.62 Mb
reserved = 2644360 KB
data = 2457536 KB
index_size = 181384 KB
unused = 5440 KB
Running the same stored proc after the change and subsequently nightly inserts over a few days returned the following,
database_size = 4872.44 Mb
unallocated space = 1.30 Mb
reserved = 3023472 KB
data = 2504888 KB
index_size = 182064 KB
unused = 336520 KB
Can anybody please help me understand why the sudden jump and what should be my course of action.
Regards,
February 24, 2011 at 1:35 pm
clustered indexes, since they are how the data is physically stored the index is in with the data not in a separate index file
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply