Post reply

Different Table size but data and table structure

  • August 26, 2002 at 9:47 am

    #64253

    I created a same table on two different server with same data. I run sp_spaceused on a table both server and I got following result

    it's useing 392 MB for table

    name rows reserved data index_size unused

    -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------

    DUNS_SITE 100000 401288 KB 400000 KB 1264 KB 24 KB

    its using only 97.3 MB

    name rows reserved data index_size unused

    -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------

    DUNS_SITE 100000 99720 KB 99376 KB 328 KB 16 KB

    WHY?????

    Thanks

    Manesh

  • August 26, 2002 at 10:09 am

    #435359

    Several issues can affect this. One is an index is incorrectly reporting its' size. Delete all the indexes and add back then see if resizes. First however try DBCC UPDATEUSAGE which

    quote:

    Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • August 26, 2002 at 10:11 am

    #435360

    Do the servers have different settings for the default index fill factor? That would do it, if you had a clustered index and the first server had a fixed small fill factor.

    To check that setting in SQL Enterprise Manager, right-click the server, and choose "Properties". In the Properties dialog, click the "Database Settings" tab.

    To check it from Query Analyzer, run this: 

    exec sp_configure 'fill factor'

    I'm guessing the server that's taking more space has a fixed 25% or lower fill factor.

    - Troy King


    - Troy King

  • August 26, 2002 at 10:31 am

    #435365

    Thanks Katracvax. I drop the index and recreated.

    Now both servers has same table size.

Viewing 4 posts - 1 through 3 (of 3 total)

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