SQL 2005 MDF File Increasing

  • 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,

  • clustered indexes, since they are how the data is physically stored the index is in with the data not in a separate index file

    http://msdn.microsoft.com/en-us/library/ms178085.aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply