SQL Server Performance

Delete Data reduce .MDF Size

Discussion in 'General DBA Questions' started by SmartSignal, Apr 8, 2005.

  1. SmartSignal New Member

    I'm looking for information on reducing the size of the .MDF file. We are not having performance issues with calls to the database but the .MDF file is 67 GIG for 1 years worth of data. What we tried to do is delete 6 months of data, 90% of our data is in 1 table 9 columns mostly float data just 500-600 million records. I successfully deleted the data, updated the stats, rebuilt all the indexes, shrink the database etc. But the .MDF only dropped to 62 GIG. I've successfully done this on other large databases but this one is giving me a headache.

    There is no clustered index on the table but there is a primary key. When I run the show_contig the three indexes look fine but the first line of the results have a scan density of 23, average free bytes 2332, average page density of 71, bestcount 157711 and actual count of 660411, logical frag 99, extent frag 14.

    The bestcount and scan density look bad to me and I know how to correct this on indexes but what about the table itself. I'm assuming that the showcontig line that is not an index is the table itself.

    1) can this be fixed
    2) will this reduce the size of the .mdf





    Patrick Murphy
    Customer/Production Support
    SmartSignal Corp
  2. Luis Martin Moderator

    I suppose those values are for table itself. Check this lookin if index id is 0.
    Mdf is 67 with space to grow or only data?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

  4. SmartSignal New Member

    Luis yes the index is 0 so I assume that it is the table itself and not the indexes. All the indexes showed minimal fragmentation and good scan densities, just not the table.<br /><br />Satya I've reviewd all these documents and they really have not helped because the indexes are fine.<br /><br />This table gets data pumped in via bulk insert every 6 minutes with 5000+ rows at the same time there is a high amount of reading the data. What I'm starting to wonder is if the fill factor is set too low. By default the fill factor is set to 60%, however I thought with the amount of data we were adding every few minutes and the use of bulk insert that lower is better.<br /><br />The other thing is there is no clustered index on the table, but I was told a primary key was just as good as a clustered index on a table. I'm begining to wonder if yes a primary key is just as good, BUT only for some maintenance things and not others.<br /><br />In this process to delete data I:<br />1) removed all indexes (except the primary key)<br />2) deleted the data<br />3) ran DBCC UpdateUsage ('SmartSignal_eCM')<br />4) ran Exec sp_createstats 'fullscan'<br />5) ran Exec sp_updatestats 'resample'<br />6) ran EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?','',0)"<br />7) ran DBCC SHRINKDATABASE (N'SmartSignal_eCM', 20)<br /><img src='/community/emoticons/emotion-11.gif' alt='8)' /> ran the backup maintenance job<br /><br />Most of the items above we do during our weekly maintenance. The odd thing is I deleted 2/3rds of the data and it only reduced the size 5 GIG out of 67. Given that the data I deleted is 90% of the data in the system I expected it to shrink much further.<br /><br />Patrick Murphy<br />Customer/Production Support<br />SmartSignal Corp
  5. SmartSignal New Member

    I'm wondering if:
    1) adding a clustered index then removing the clustered index would work

    2) or instead of deleting that much data in the future rename the table, create a new table and copy the good data from the old table to the new table would be faster and more beneficial.



    Patrick Murphy
    Customer/Production Support
    SmartSignal Corp
  6. satya Moderator

    True create a clustered index and deleting will have the updated stats on all other non clustered indexes.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Luis Martin Moderator

    Shrink database as last step affect all reindex and update statistics you did before.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page